Returnig a value
 

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

Returnig a value
 

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="B B")*(ROWS($A$1:$A$4)))-1,0)

--
Gary''s Student - gsnu200854


"peyman" wrote:

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

Returnig a value
 

THANKS FOR YOUR REPLY BUT THE FORMULA IS NOT GIVING ME RIGHT ROW NUMBER!!

"Gary''s Student" wrote:

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="B B")*(ROWS($A$1:$A$4)))-1,0)

--
Gary''s Student - gsnu200854


"peyman" wrote:

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

Returnig a value
 

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

"Gary''s Student" wrote:

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="B B")*(ROWS($A$1:$A$4)))-1,0)

--
Gary''s Student - gsnu200854


"peyman" wrote:

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