Multi-Conditional VLOOKUP

D

Dendrinos2

New Member
Joined
Nov 2, 2011
Messages
30
I am trying to do a VLOOKUP or equivalent to search the attached data table and display the cost of the Grapes/White/Bitter which cost 4

I am having trouble because there is multiple criteria that is the same. First there are multiples of the same fruit then there are multiples of the same type. The taste is the criteria that separates them and I need to display the cost in a new cell.

Fruit Type Taste Cost
Grapes Purple Sweet 1
Grapes Purple Sour 2
Grapes White Salty 3
Grapes White Bitter 4
Apples Red Sweet 5
Apples Red Salty 6
Apples Green Bitter 7
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Dendrinos2 said:
I am trying to do a VLOOKUP or equivalent to search the attached data table and display the cost of the Grapes/White/Bitter which cost 4

I am having trouble because there is multiple criteria that is the same. First there are multiples of the same fruit then there are multiples of the same type. The taste is the criteria that separates them and I need to display the cost in a new cell.

Fruit Type Taste Cost
Grapes Purple Sweet 1
Grapes Purple Sour 2
Grapes White Salty 3
Grapes White Bitter 4
Apples Red Sweet 5
Apples Red Salty 6
Apples Green Bitter 7
Click to expand...
Control+shift+enter, not just enter:

=INDEX($D$2:$D$8,MATCH(1,IF($A$2:$A$8=F2,IF($B$2:$B$8=G2,IF($C$2:$C$8=H2,1))),0))

where F2 = Grapes, G2 = White, and H2 = Bitter.
 
Upvote 0
You need a helper column... try it like this. That is to say, combine all the stuff you want to search into one column, then use the vlookup to search that instead, or thats how i do it)


<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0"><b>A</b></td><td align="center" bgcolor="#C0C0C0"><b>B</b></td><td align="center" bgcolor="#C0C0C0"><b>C</b></td><td align="center" bgcolor="#C0C0C0"><b>D</b></td><td align="center" bgcolor="#C0C0C0"><b>E</b></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>1</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Fruit </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Type </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Taste </font></td><td rowspan="1" colspan="1" width="396" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Helper one </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Cost </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>2</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Grapes </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Purple </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Sweet </font></td><td rowspan="1" colspan="1" width="396" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=A2&B2&C2 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">1 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>3</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Grapes </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Purple </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Sour </font></td><td rowspan="1" colspan="1" width="396" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=A3&B3&C3 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>4</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Grapes </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">White </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Salty </font></td><td rowspan="1" colspan="1" width="396" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=A4&B4&C4 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">3 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>5</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Grapes </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">White </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Bitter </font></td><td rowspan="1" colspan="1" width="396" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=A5&B5&C5 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">4 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>6</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Apples </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Red </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Sweet </font></td><td rowspan="1" colspan="1" width="396" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=A6&B6&C6 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">5 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>7</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Apples </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Red </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Salty </font></td><td rowspan="1" colspan="1" width="396" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=A7&B7&C7 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">6 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>8</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Apples </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Green </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Bitter </font></td><td rowspan="1" colspan="1" width="396" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=A8&B8&C8 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">7 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>9</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="396" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>10</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Search </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="396" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>11</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Fruit </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Type </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Taste </font></td><td rowspan="1" colspan="1" width="396" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Cost </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>12</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Grapes </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Purple </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Sour </font></td><td rowspan="1" colspan="1" width="396" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=VLOOKUP(A12&B12&C12,D1:E8,2,FALSE) </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000"> </font></td></tr>
</table>
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Filtering a list of subscribers based on their preferences
Replies
1
Views
460
SanjayGMusafir
SanjayGMusafir
N
  • Question Question
Find Latest comment for ID using vlookup/MAX?
Replies
1
Views
355
KOKOSEK
KOKOSEK
P
  • Question Question
Applying conditional formatting to merged cells
Replies
1
Views
699
Joe4
Joe4
G
  • Question Question
Copy Range of Cells based on Criteria, Paste it on rows below it, Do This Until All conditions are met, Then Delete All Copied Rows
Replies
0
Views
588
glaccounting
G
S
  • Question Question
How to sum and rank with multi criteria uniquely without duplicate? For excel 2019
Replies
3
Views
561
Sochen
S

Forum statistics

Threads
1,225,845
Messages
6,187,360
Members
453,420
Latest member
ESCH1021

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top