| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
