Multiple Criteria lookup? (multiple combinations)

[curiousgeorgeuk]

Firstly, Thank you for accepting on the board, New here. Have a reasonable understanding to Excel, though I have come across a something I just cant figure out.

I will try explain as clear as possible and what I am trying to do.

I am trying to populate column 10 with a value from another sheet based on criteria from column 1, 2, 3.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column 1 [/TD]
[TD]2 [/TD]
[TD]3 [/TD]
[TD]4 [/TD]
[TD]5 [/TD]
[/TR]
[TR]
[TD]Test 1 [/TD]
[TD]V1[/TD]
[TD]B1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 2 [/TD]
[TD]V2[/TD]
[TD]B2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 3[/TD]
[TD]V3[/TD]
[TD]B3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 4 [/TD]
[TD]V4[/TD]
[TD]B4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 5 [/TD]
[TD]V5[/TD]
[TD]B5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The above sheet is a simplified version of my sheet, I want to return a result in column 5 based on ANY combination of Column 1, 2, 3 from sheet 2 (see table below

[TABLE="width: 500"]
<tbody>[TR]
[TD]Test 1 [/TD]
[TD]V1 [/TD]
[TD]B1[/TD]
[TD]£100.00[/TD]
[/TR]
[TR]
[TD]Test 1 [/TD]
[TD]V2[/TD]
[TD]B1[/TD]
[TD]£50.00[/TD]
[/TR]
[TR]
[TD]Test 1 [/TD]
[TD]V3[/TD]
[TD]B1[/TD]
[TD]£25.00[/TD]
[/TR]
</tbody>[/TABLE]


I would normally do this with vlookup and if statement, but that would just get crazy long.

Hope that makes sense.

Thanks

******** type="text/javascript" src="safari-extension://com.ebay.safari.myebaymanager-QYHMMGCMJR/e3b22f70/background/helpers/prefilterHelper.js">*********>******** type="text/javascript" src="safari-extension://com.ebay.safari.myebaymanager-QYHMMGCMJR/e3b22f70/background/helpers/prefilterHelper.js">*********>

 

[Eric W]

Welcome to the board.

In J1:

=IFERROR(INDEX(Sheet2!$D$1:$D$10,MATCH(A1&"|"&B1&"|"&C1,Sheet2!$A$1:$A$10&"|"&Sheet2!$B$1:$B$10&"|"&Sheet2!$C$1:$C$10,0)),"No match")
and confirm it with Control+Shift+Enter. Depending on your regional settings, you may need to change the commas to semicolons.

 

[curiousgeorgeuk]

Thankyou!

Took a couple of attempts to modify to my sheet and wording, but once done works perfect!

******** type="text/javascript" src="safari-extension://com.ebay.safari.myebaymanager-QYHMMGCMJR/e3b22f70/background/helpers/prefilterHelper.js">*********>******** type="text/javascript" src="safari-extension://com.ebay.safari.myebaymanager-QYHMMGCMJR/e3b22f70/background/helpers/prefilterHelper.js">*********>

 

[Eric W]

Cool! Glad you got it working!