Extract data from unknown lookup range

{10 Comments}

The VLOOKUP() function accepts a definite lookup array (second input in the VLOOKUP()) specified by the end-user.  In a situation where the lookup array is unknown, a function may have to cycle through various lookup ranges to cull out the required value.

You may refer to my solution in this workbook.

RSS 10 Comments…

 Share your views
  1. I am facing the situation wherein the same kind of data range exists across multiple worksheets in the same excel workbook. I need to prepare a summary sheet wherein the lookup needs to be made from one of the multiple worksheet and I can specify the name of the worksheet the data needs to be picked from everytime I am applying vlookup.

    Request you to please help me with this situation.

    • Hi,

      1. Suppose data in range A2:C5 of "sheet1" is in range A2:C5 of "Data1" sheet
      2. Suppose data in range E2:G5 of "sheet1" is in range E2:G5 of "Data2" sheet
      3. Suppose data in range I2:K5 of "sheet1" is in range I2:K5 of "Data3" sheet
      4. In range C13:C15 of sheet1, type Data1, Data2 and Data3
      5. In cell B13, enter =VLOOKUP(A13,INDIRECT("'"&C13&"'!A3:C5"),3) and copy down. If you are looking for an exact match, enter
      =VLOOKUP(A13,INDIRECT("'"&C13&"'!A3:C5"),3,0)

  2. Hi... What is the role of Rept function here? Why 99 times of Z is required?

    • Hi,

      =REPT("Z",99) generates a series of 99 z's i.e. zzzzzzz all the way till 99. This is a very large string. When passed as an input to the LOOKUP() function, REPT function will force a search from right to left or bottom to top. The LOOKUP() function has a certain property to lookup from right to left or bottom to top when the lookup_value is greater than the maximum/longest value/string which can appear in the lookup_range. Since there can be nothing greater than 99 z's, the search will happen from right to left or bottom to top and in doing such a search numbers, blanks and error will be ignored.

      Hope this clarifies.

  3. So. Here this "Z" makes inverse lookup. Am I right?

  4. Could you help me please. Just trying to create multiple worksheets from a master. I would like to capture data from Column A and Column B and auto populate to a worksheets called Education , Work Placement, Telephone

    Name Surname Education Work placement Telephone
    Brian Thomas St. Marks Nypro 1234567890

Leave a Comment

Your email address will not be published.

*