Vlookup areas?

[Sam]

Hello All,

I use Vlook up all the time but i only ever look up from one tab to another
tab, does anyone know if its possible to look up something in all tabs at the
same time?

For example i want to see if items column A on spreedsheet one are repeated
in column A, B or C in spreedsheet two...

Thanks.

 

[Pete_UK]

One general approach:

=IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),IF(ISNA(vlookup_3),"not
present",vlookup_3),vlookup_2),vlookup_1)

where vlookup_1 is looking at table_1, vlookup_2 is looking at table_2
etc, and table_1 and table_2 are not necessarily in the same sheet.

In your particular case, though, you can use MATCH:

=IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"","A") & IF(ISNA(MATCH(A2,Sheet2!B:B,
0)),"","B") & IF(ISNA(MATCH(A2,Sheet2!C:C,0),"","C")

Will return A, B or C if A2 in Sheet1 is found in any of those columns
in Sheet2.

Hope this helps.

Pete

 

[L. Howard Kittle]

Hi Sam,

I got this from Peo Sjoblom some time ago. Even with some e-mailed details
on how it works I cannot figure it all out. The example he sent me covered
8 worksheets, but that is just a matter of number of sheets you list in the
named range MySheets. The first formula is the original from Peo, the
second is the same which I modified to trap errors and return "".

The first one looks up the value of A2 on the main sheet and searches all
sheet names in MySheets A2:A200 in each and returns the third column value.
The second one does essentially the same thing with error trapping and looks
up the value in C3 and returns column 2 value.

You will need to make a list of all the sheets you want to look up and name
it MySheets (or whatever, if other than MySheets then use your name in the
formula instead of MySheets).

Of course adjust the ranges to suit your sheet needs.

Use Ctrl+Shift+Enter to commit the formula. (Array Enter)

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

=IF(ISNA(VLOOKUP(C3,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:F58"),C3)>0),0))&"'!A2:F58"),2,0)),"",VLOOKUP(C3,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:F58"),C3)>0),0))&"'!A2:F58"),2,0))

Looks a bit intimidating but works great.

HTH
Regards,
Howard

 

[Sam]

Hi Howard,

Your right it looks very intimidating, so i have lots of tabs and will add
to them everymonth, for example my tabs are actually number 8269, 8268, 8267
etcso i f i create a sheet with this formula in it (i guess i can actually
keep in in the same workbook) what formula would i be looking to start with??
sorry blonde moment..

Thanks,
Sam.

 

[L. Howard Kittle]

Hi Sam,

I reread your post and I probably offered the wrong solution.

<For example i want to see if items column A on spreedsheet one are repeated
in column A, B or C in spreedsheet two...>

Perhaps something like this...?

=Vlookup(A1,Sheet2!A1:C10,1,0)
=Vlookup(A1,Sheet2!A1:C10,2,0)
=Vlookup(A1,Sheet2!A1:C10,3,0)

I may not understand your question, The first part you want to lookup in
several sheets and the second part you want to verify if a value is columns
A, B and C of the second sheet.

Regards,
Howard