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.
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)
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.
So. Here this “Z” makes inverse lookup. Am I right?
No. 99 z’s force an inverse lookup.
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
Hi,
Your question is not clear. Share the link from where I can download the workbook and please explain the question again.
https://docs.google.com/spreadsheets/d/1GMwHYmTE99OcgqtaXAWMFK9Orxu1MnvYxwps_FvZ5dY/edit?usp=sharing
Would like all data to be dynamic, thanks again for your assistance.
Hi,
You may refer to my solution in this workbook. To create other worksheets, try this
1. Right click on the WD Auto LC worksheet
2. Select Move or Copy
3. Check the box for Copy
4. Rename the worksheet to WD Auto Eng
Hope this helps.
Excellent, i knew i could count of you. Thank you for your assistance.
You are welcome.
Hi Ashish,
Could you tweak the formula. I would like to create a separate work book e.g. WD Auto LC, WD Auto Eng etc.
Many thanks
Hi,
It will not be possible to solve that with a formula because the INDIRECT() function will not work if the workbook with the Master worksheet is closed. As long at that workbook remains open, the formula will yield the correct result. The only way to resolve this problem with a formula is to download and use the Morefunc add-in and use the INDIRECT.EXT function.
While the link shared above is of my site only, I will not be able to help you with re-writing the formula using INDIRECT.EXT because that add-in is only supported in Excel 32 bit. I used Excel 2013 64 bit.
Extract data from unknown lookup range
{ 14 Comments }