Extract data from unknown lookup range


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 6 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

  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?

Leave a Comment

Your email address will not be published.