Returnig a value

P

peyman

Hi,
I have a sample data reference file like below:

column A column B column C

055555 AA 1000
088888 BB 5600
077777 CC 6000
033333 BB 2000

now if I have the first 2 columns in another file , how can I write a
formula to return me the corresponding column C value in reference file if
the values in the first 2 columns in second file and the ref. file are
identical. for example:

in second file:
if:
column A column B
033333 BB
then in the column C the formula returns 2000.
I hope I'm clear on that.
Thanx,
Peiman
 
G

Gary''s Student

Whenever you need to lookup multiple criteria, you can use SUMPRODUCT to find
the correct row and then use OFFSET to use that row on a different column:

=OFFSET($C$1,SUMPRODUCT((A1:A4="033333")*(B1:B4="BB")*(ROWS($A$1:$A$4)))-1,0)
 
P

peyman

I found out why the formula is not giving correct row number. ROWS in the
formula should be changed to ROW.
Thanx anyway.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top