Lookup data in multiple cell range

  • Thread starter Thread starter DejanFlo
  • Start date Start date
D

DejanFlo

Hello,

I have to find data in multiple cell range and return corresponding value:

Example: I have 3 tables with same type of data, but diferent lookup subject.

Then I have to find subject in any of 3 ranges and get corresponding values
for that record.

Regards
 
Hi,
would help an example but as an start, if you need to look for the name
entered in cell A1, in table1 and assuming that table 1 is a different sheet
where you have the name you are looking for in column A and the ammount in
column B something like this will do the trick

=sumproduct(--(A1=table1!$A$1:$A$1000),table1!$B$1:$B$1000)
 
Try something like this, named ranges use for simplicity

=IF(AND(ISERROR(VLOOKUP(B3,rng1,2,0)),ISERROR(VLOOKUP(B3,rng2,2,0))),VLOOKUP(B3,rng3,2,0),IF(AND(ISERROR(VLOOKUP(B3,rng2,2,0)),ISERROR(VLOOKUP(B3,rng3,2,0))),VLOOKUP(B3,rng1,2,0),VLOOKUP(B3,rng2,2,0)))

Please tick Yes if this helps

Peter Atherton
 
Are you saying there could be matches in any or all of the ranges. Or, just
ONE?
You may want to use MATCH & INDEX instead.
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.
 
Back
Top