Vlookup multiple Criterias

virtech2k3 · Jun 29, 2011

Column A B C D E F
Price Type Rooms Price Rooms
123000 Family 4 124000 3
121000 Family 3 121000 2
125000 Condo 1 123000 3
121000 Dorm 1 121000 2
126000 Dorm 2 125000 1
124000 Family 3 124000 4
125000 Condo 2 126000 1

Can someone please help?

I want to do a Vlookup formula above. I want to find all houses Priced $125000 (Column E), but that also has 1 ROOM (Column C) and return the HOUSE type. (B)

In other words, I want excel to return the HOUSE TYPE (column B above) priced $125000, only if the ROOM (Row F ) corresponds to column C.

In the Above Example, Excel should give me Type (CONDO) because it has 1 room and $1250000 price (perfect multiple match)

Please let me know if you need more information as I am rather new at this.

shadow12345 · Jun 29, 2011

The data hasn't come out to clearly I don't think..... i show two Prices and two room counts.

Column E isn't Price its the Second Room count.

Could you explain?

virtech2k3 · Jun 29, 2011

shadow12345 said:
The data hasn't come out to clearly I don't think..... i show two Prices and two room counts.

Column E isn't Price its the Second Room count.

Could you explain?

Sorry about that.

Column A= Price(1st) B=Type C=Rooms(1st) D=Price(2nd) E=Rooms(2nd) F=Wants the returned Type from Column B only if Price 2nd and rooms 2nd match price 1st and rooms 1st.

Please let me know if this clears up more and thanks for your help.

shadow12345 · Jun 29, 2011

This may not be the cleanest solution but it works. Add a blank col at the start and combine the Price 1 and Rooms 1

Then use the below formula to get what you want in Col G

*Helper col A formula
=B2&D2

** Col G Formula
=IF(ISNA(VLOOKUP(E2&F2,$A$2:$D$8,3,FALSE))=TRUE,"",VLOOKUP(E2&F2,$A$2:$D$8,3,FALSE))

<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0">A</td><td align="center" bgcolor="#C0C0C0">B</td><td align="center" bgcolor="#C0C0C0">C</td><td align="center" bgcolor="#C0C0C0">D</td><td align="center" bgcolor="#C0C0C0">E</td><td align="center" bgcolor="#C0C0C0">F</td><td align="center" bgcolor="#C0C0C0">G</td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">1</td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Helper </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Price </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Type </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Rooms </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Price </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Rooms </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">2</td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">=B2&D2 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">123000 </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Family </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">4 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">124000 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">3 </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">=IF(ISNA(VLOOKUP(E2&F2,$A$2:$D$8,3,FALSE))=TRUE,"",VLOOKUP(E2&F2,$A$2:$D$8,3,FALSE)) </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">3</td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">=B3&D3 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">121000 </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Family </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">3 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">121000 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">2 </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">=IF(ISNA(VLOOKUP(E3&F3,$A$2:$D$8,3,FALSE))=TRUE,"",VLOOKUP(E3&F3,$A$2:$D$8,3,FALSE)) </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">4</td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">=B4&D4 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">125000 </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Condo </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">1 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">123000 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">3 </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">=IF(ISNA(VLOOKUP(E4&F4,$A$2:$D$8,3,FALSE))=TRUE,"",VLOOKUP(E4&F4,$A$2:$D$8,3,FALSE)) </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">5</td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">=B5&D5 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">121000 </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Dorm </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">1 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">121000 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">2 </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">=IF(ISNA(VLOOKUP(E5&F5,$A$2:$D$8,3,FALSE))=TRUE,"",VLOOKUP(E5&F5,$A$2:$D$8,3,FALSE)) </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">6</td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">=B6&D6 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">126000 </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Dorm </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">2 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">125000 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">1 </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">=IF(ISNA(VLOOKUP(E6&F6,$A$2:$D$8,3,FALSE))=TRUE,"",VLOOKUP(E6&F6,$A$2:$D$8,3,FALSE)) </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">7</td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">=B7&D7 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">124000 </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Family </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">3 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">124000 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">4 </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">=IF(ISNA(VLOOKUP(E7&F7,$A$2:$D$8,3,FALSE))=TRUE,"",VLOOKUP(E7&F7,$A$2:$D$8,3,FALSE)) </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">8</td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">=B8&D8 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">125000 </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">Condo </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">2 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">126000 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF">1 </td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF">=IF(ISNA(VLOOKUP(E8&F8,$A$2:$D$8,3,FALSE))=TRUE,"",VLOOKUP(E8&F8,$A$2:$D$8,3,FALSE)) </td></tr>
</table>

virtech2k3 · Jun 29, 2011

Shadow,

This works perfectly with this data and thank you for your precise advice.

My question though goes outside the scope of this data and I tried to use it as example. What I am ultimate trying to do is compare two sets of data when they match perfectly, I thought this quick example would help and it does for this particular item.

But I guess my MAIN question is, is there a way to do a VLOOKUP for say 5 columns, only when the other set of data matches the 5 columns perfectly?

The report I am working with comes from 2 different databases and I only need the ones where 5 different sets of columns from one report, match the exact same 5 sets on the other report.

It gets complicated as it contains over 40 columns and 500 rows of data, so I was trying to get a set method from this small example and just expanding to my massive report.

shadow12345 · Jun 29, 2011

You can combine as many cols as you want in your helper col and then just modify the Vlookup to look for matching cols in the second file.

For example
*Helper col A formula
=B2&D2&F2&G2

** Col G Formula
=IF(ISNA(VLOOKUP(B2&D2&F2&G2,$A$2:$D$8,3,FALSE))=TRUE,"",VLOOKUP(B2&D2&F2&G2,$A$2:$D$8,3,FALSE))

virtech2k3 · Jun 29, 2011

This is amazing, I will try this tomorrow and thank you!

Vlookup multiple Criterias

virtech2k3

New Member

Excel Facts

shadow12345

Well-known Member

virtech2k3

New Member

shadow12345

Well-known Member

virtech2k3

New Member

shadow12345

Well-known Member

virtech2k3

New Member

Similar threads

Forum statistics

Share this page

Vlookup multiple Criterias

virtech2k3

New Member

Excel Facts

shadow12345

Well-known Member

virtech2k3

New Member

shadow12345

Well-known Member

virtech2k3

New Member

shadow12345

Well-known Member

virtech2k3

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock