Vlookup function too long
Hi,
I'm trying to vlookup eleven worksheets with various ranges. After typing in all the vlookups, the formula is too long to save. Any ideas?
Eg:
=IF(ISNA(VLOOKUP(A2,'2.0 X 1.5'!$A$4:$C$4,3,FALSE)),VLOOKUP(A2,'2.25 X 3.25'!$A$3:$C$14,3,FALSE),VLOOKUP(A2,'3 X 4'!$A$3:$C$12,3,FALSE))
Couldn't go any further on the function.
I appreciate any assistance.
Thank you
Last edited by NBVC; 03-10-2010 at 02:47 PM.
If you can make all of the ranges the same sizes, even if they end up including blank rows, and make sure you use the largest range of the 11 sheets, then you can do this.
List all 11 sheetnames in your summary sheet somewhere, then select that range and go to Formula tab and Define Name... Enter the word: Tabnames in the Name field.
Then apply formula like:
where 14 in the formula represents the last row of the largest lookup table in your 11 sheets.. and A3 represents top most cell in the same tables...=VLOOKUP(A2,INDIRECT("'"&INDEX(TabNames,MATCH(TRUE,COUNTIF(INDIRECT("'"&TabNames&"'!A3:A14"),A2)>0,0))&"'!A3:C14"),3,FALSE)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I still can't get the formula to work.
I've arranged every sheet so that the data will begin and end on the same lines including blanks, so the range fields are the same for each sheet.
I've entered this formula, creating a new sheet called "MySheets" as a summary of all the worksheets.
=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&MySheets&"'!A3:A14"),A2)>0,0 ))&"'!A4:C31"),3,FALSE)
Do I need a create a "Name Manager" for the Mysheets?
Thank you.
Yes you need to create a Named Range called MySheets which points to the cells that contain the 11 sheetnames.
and revise formula a bit:
=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&MySheets&"'!A3:A31"),A2)>0,0 ))&"'!A4:C31"),3,FALSE)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I've created a named range called MySheets pointing to the 11sheetnames Revised the formula as follows:
=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&MySheets&"'!A2:A31"),A2)>0,0 ))&"'!A4:C31"),3,FALSE)
Still can't get it to work. Copied and paste special value into cell A2 to verify I wasn't typing it incorrectly.
Did you confirm it with CTRL+SHIFT+ENTER keys together, not just ENTER?
I forgot to mention that earlier
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thank you so much. i greatly appreciate the help.
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks