Vlook up or if statement helps!

J

Jurassien

Hello,
Can anyone assist me in finding out what would be the best way to search for
a matching value within 5 different worksheets simultaneously?

Thanks,
 
B

Barb Reinhardt

If you could create a named range containing the data on the 5 worksheets,
you could use something like VLOOKUP.
 
L

L. Howard Kittle

Hi Jurassien,

I got this from Peo Sjoblom. Which is an array formula... use Ctrl + Shift
+ Enter to commit. You will get { }brackets around the formula if done
correctly.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

Where A2 is the lookup value. (adjust to suit)

MySheets is a named range of the 5 worksheets you want to lookup. (Make a
list of the sheet names, select them and in the name box type in your
choosen name.)

A2:C200 is the Lookup Array of each sheet. (adjust to suit)

The ...,3,0) returns the value from the 3rd column, if you have two columns
then change to 2, and the ...,0) says to look for an exact match.

HTH
Regards,
Howard
 
Top