Lookup data in multiple cell range

[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

 

[Eduardo]

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)

 

[Billy Liddel]

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

 

[Don Guillett]

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.