Match 2 columns in 2 sheets and return the 3rd column

Hi,

I have to match 2 columns with 2 columns of other sheet and return the 3rd column value from the second sheet to the first sheet. Tried MATCH but has result has multiple values. Here are the details

Sheet 1

Column I and Column K values should match with

Sheet 2

Column N and Column B to get the coulmn F value.

But the catch is that Column N and B would return 2 rows with Column F values either 0 or any value. I need to take the row with the column F value and not the 0.

How can i do that using excel formulae?

Thanks

Perhaps something like:

=SUMPRODUCT(--('Sheet2'!$N$2:$N$100=I2),--('Sheet2'!$B$2:$B$100=K2),'Sheet2'!$F$2:$F$100)

adjust ranges to suit (can't use whole columns with this formula in XL2003).

The -- coerces the TRUE/FALSE results to 1's and 0's then these arrays are multiplied together to get results of 1's and 0's, then the 1/0 array is multiplied by the results and where the 1 matches a number in column F, that value will be cumulatively summed.

The ranges should all be the same size.. you were using C:C to sum against small check ranges... and you didn't put in the $ signs.

Try:

=SUMPRODUCT(--(Sheet1!$A$2:$A$13= Sheet2!A2),--(Sheet1!$B$2:$B$13=Sheet2!B2),Sheet1!$C$2:$C$13)

also, column A of Sheet1 has many extra spaces at the ends of the entries.

Try removing them, you can do Ctrl+H and then Find What: enter a space and click Replace All.

After following the instructions I gave you, the attached should be the result.

You are welcome,

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save