If 2 columns match - return value

[E5254730]

Hello Excel Experts,

If Column 1 and Column 2 match - than return the value which is in column 3

For eg ; Please refer to attached sheet.

Regards,
Prakash

[ChemistB]

Three options.
1. In Column A starting at A4 copied down

=B4&C4
Then use =VLOOKUP(B18&C18, $A$4:$D$15,4, FALSE)

2. Use an Arrayed formula
=INDEX($D$4:$D$15, MATCH(B19&C19, $B$4:$B$15&$C$4:$C$15,0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

3. Use this formula
=INDEX($D$4:$D$15, MATCH(B20&C20,INDEX(B4:B15&$C$4:$C$15,),0))

I believe #1 is the most efficient if you have large amounts of data.
See attached where I used all 3 formulas.

[ben_hensel]

Like this?

Please Login or Register  to view this content.

Notes:
This will return zero if there are no matches of name / part number
If there are non-unique entries (eg, there are two places the "A, 2, 20" combo is listed), those will be summed up (for 40 for example)

[E5254730]

ChemistB,

Thanks....I applied all the formulas to my vast data....and it is working to a maximum limit.

Cheers

Many Thanks,
Peggy