Looking For Lookup Help

  • Thread starter Thread starter jocabeana
  • Start date Start date
J

jocabeana

I am hoping that someone can help me figure out a problem that i am
having using the VLOOKUP function.
Say I have a 5 worksheet workbook and on the SHEET 1 i set up my lookup
formula to look up the value in column A and return it to column B. I
can do that but here's where it gets tricky.
I want it to initially look up the value on SHEET 2 and then if it's
not there to go on to SHEET 3 then SHEET 4 etc.
It will be looking up text values but each one will be unique so it
shouldn't ever find more than one value to return.
I would appreciate any input on this
Thanks
Jason
 
You can easily nest a VLOOKUP into an IF statement however there is
limit of 7 "IF"s you can nest plus the fact that this type of formul
gets very large in a hurry.

Your best bet is to get the excel add-in that contains the THREE
function which allows you to put in a 3D array in VLOOKUP function.
just recently downloaded this free add-in and the THREED function work
like a charm. If you are trying to lookup "X" in column A and pull i
the data from column C (AND YOU KNOW IT IS IN THE 1ST 100 ROWS) over
range of sheets, your VLOOKUP would look like this:

VLOOKUP("X",Sheet1:SheetX!A1:C100,3,False)

The value in column C will be returned in the first row of the firs
sheet where X is founD in the 3D array Sheet1:SheetX!A1:C5



Download and install Laurent Longre's MOREFUNC.XLL add-in @
http://longre.free.fr/english
 
Ah yes, the all important THREED function, that was my main point and
missed it. Oh well its lat
 
Back
Top