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)

"DejanFlo" wrote:

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

Try something like this, named ranges use for simplicity

=IF(AND(ISERROR(VLOOKUP(B3,rng1,2,0)),ISERROR(VLOO KUP(B3,rng2,2,0))),VLOOKUP(B3,rng3,2,0),IF(AND(ISE RROR(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

"DejanFlo" wrote:

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

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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DejanFlo" wrote in message
...
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