Lookup and or Index Match with If Function?

[Bedford]

I have a workbook with 2 sheets, in sheet 2 are a list of product codes with corresponding price in 2 currencies. In sheet 1 a drop-down list of the product codes and a drop-down list of the 2 currencies.

As it is now, in sheet 1, I select the product code from a list and with the below formula I am only able to pull the “USD List” price regardless if I select “USD List” or “CAD List” because I don’t know how to write the formula to identify where to draw the data from.

=IF(ISNA(VLOOKUP(A2,'Sheet 2'!$B$2:$C$4,2,FALSE)),"",VLOOKUP(A2,'Sheet 2'!$B$2:$C$4,2,FALSE))

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>







[TABLE="class: tableizer-table"]
<thead>[TR="class: tableizer-firstrow"]
[TH][/TH]
[TH]SHEET 1[/TH]
[TH] [/TH]
[TH] [/TH]
[/TR]
</thead><tbody> [TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product Code[/TD]
[TD]Currency[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]VB-444x612-2[/TD]
[TD]CAD List[/TD]
[TD]$10.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]MM-898x728-3[/TD]
[TD]USD List[/TD]
[TD]$15.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>







[TABLE="class: tableizer-table"]
<thead>[TR="class: tableizer-firstrow"]
[TH][/TH]
[TH]SHEET 2[/TH]
[TH] [/TH]
[TH] [/TH]
[/TR]
</thead><tbody> [TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product Code[/TD]
[TD]USD List[/TD]
[TD]CAD List[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]VB-444x612-2[/TD]
[TD]$10.00[/TD]
[TD]$11.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]MM-898x728-3[/TD]
[TD]$15.00[/TD]
[TD]$16.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]MS-999x2142-1S[/TD]
[TD]$12.00[/TD]
[TD]$13.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Try changing the VLOOKUP part of your formula to:

VLOOKUP(A2,Sheet2!$A$2:$C$4,MATCH(B2,Sheet2!$A$1:$C$1,0),0)

 

[etaf]

=IFERROR(IF(B2="cad list", VLOOKUP(A2,Sheet2!$A$1:$C$4,3,FALSE),VLOOKUP(A2,Sheet2!$A$1:$C$4,2,FALSE)),"")

Beaten to it

to stop the #N/A error
=IFERROR(VLOOKUP(A2,Sheet2!$A$2:$C$4,MATCH(B2,Sheet2!$A$1:$C$1,0),0),"")

 

[Bedford]

Try changing the VLOOKUP part of your formula to:

VLOOKUP(A2,Sheet2!$A$2:$C$4,MATCH(B2,Sheet2!$A$1:$C$1,0),0)

AhoyNC, Wayne, thank you for your help, it worked well on the simple example I had included. I actually created that sample thinking any response could be transposed to the more complicated workbook where I have the actual challenge, but that’s not the case it seems. I’ll try to explain the actual scenario if you can bare with me.

Using the response you sent, I adjusted the formula to read;

=IFERROR(VLOOKUP(J8,'Customer Product'!$A$6:$N$400,MATCH(M8,'Customer Product'!$M$1:$N$1,0),0),"")

The formula does return a result, however from the wrong column in the sheet ‘Customer Product’. The results I’m looking for, “USD List” and or “CAD List” are located in columns “M” and “N” respectively, in the Customer Product sheet. The results being returned are from columns “A” and “B”? I’m guessing the issue is with “J8” in the formula as it’s highlighted in blue, I’ve tried numerous adjustments without success.

Again, your help would be appreciated.

 

[AhoyNC]

Try changing your formula to:

=IFERROR(VLOOKUP(J8,'Customer Product'!$A$6:$N$400,MATCH(M8,'Customer Product'![COLOR="#FF0000"]$A$1:$N$1[/COLOR],0),0),"")

 

[Bedford]

Ok, that's amazing! Works like a charm, thank you very much again...
Doug.