Looking up values across tabs

[wrethams]

I have a spreadsheet with a list of codes on separate tabs, each tab
is named according to its category .

On the last tab I have a list containing of ALL the above codes.

I want a formula to look at all of the tabs (apart from the final one)
and return the category for each code next to the relevant code in the
list on the last tab.

The end result is that each code in the complete list will have the
correct category next to it.

Any help appreciated.

 

[Guest]

Hi,

I think I have a similar problem.

I have a single (large) workbook, with multiple worksheets (all same layout
and format).

These multiple sheets show data being returned from multiple sources. I have
to cross-check another sheet of unique reference numbers to see if they exist
in any of the multiple sheets.

I've been using the VLOOKUP function with success, but to do this I have
been consolidating (Using cut & paste) the multiple sheets into one. The
multiples are getting larger and larger (over 15 sheets) now. Is there a way
of searching for a cells contents (lookup_value) across multiple data sources
(Table_array across multiple sheets) and returning a result (or even a #N/A -
at least I'd know if the unique ref exists!).

If there's another function other than VLOOKUP then I'm happy to adapt!

L.

 

[Lori]

If codes are integers in column A of sheets 1-3 try filling down:

=MATCH(2,1/(FREQUENCY(A2+1,Sheet1:Sheet3!
A:A)>FREQUENCY(A2,Sheet1:Sheet3!A:A)))

This gives the relative position of the value across the sheets. From
this you can infer the sheet e.g. if each sheet contains 10 IDs a
value of 25 would indicate the fifth row of the third sheet.

To check for duplicate values you could fill down:

=INDEX(FREQUENCY(Sheet1:Sheet3!A:A,A2-{1,0}),2)

 

[Lori]

Try instead of the first formula above:

=MATCH(2,1/(FREQUENCY(A2+{-0.1,0,0.1},Sheet1:Sheet3!A:A)=2))