Vlookup multiple columns

Bunnygum

New Member
Joined
Mar 5, 2009
Messages
5
Hi,

I am having trouble with the syntax for this.

=VLOOKUP(B20, SpanCatForce,
IF(AND(C20="4%"), 2,
IF(AND(C20="5%"), 3,
IF(AND(C20="6%"), 4,
IF(AND(C20="7%"), 5,
IF(AND(C20="8%"), 6,
IF(AND(C20="9%"), 7,
IF(AND(C20="10%"), 8,
IF(AND(C20="11%"), 9,
IF(AND(C20="12%"), 10,
IF(AND(C20="13%"), 11,
IF(AND(C20="14%"), 12,"check"))))))))))))

I am trying to look up data in multiple columns where B20 contains the line value and C 20 determines which column to use

Any assistence would be appreciated
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
Because you seem to have a consistent pattern here, perhaps something like
Code:
=vlookup(b20,spancatforce,(c20*100)-2,false)
 
Upvote 0

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Perhaps something like this:

Code:
=IF(MEDIAN(0.04,C20,0.14)=C20,VLOOKUP(B20,SpanCatForce,INT(C20*100)-2),"CHECK")

Edited to include this less arcane approach:
Code:
=IF(AND(C20>=0.04,C20<=0.14),VLOOKUP(B20,SpanCatForce,INT(C20*100)-2),"CHECK")
Is that something you can work with?
 
Last edited:
Upvote 0

Bunnygum

New Member
Joined
Mar 5, 2009
Messages
5
Hi,

I do not think I have explained myself very well.

The following examples may explain what I am trying to achieve.

If B20 contains the value 4.5 and C20 contains the value 8% return the value where this row and columns intersect. Ie 4.99
Another example, If B20 contained the value 4.75 and C20 contained the value 12% return the value where this row and columns intersect. Ie 3.69.

See table below
<table style="border-collapse: collapse; width: 624pt;" width="832" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="13" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20"></td> <td class="xl66" style="width: 48pt;" width="64" align="right">4%</td> <td class="xl66" style="width: 48pt;" width="64" align="right">5%</td> <td class="xl66" style="width: 48pt;" width="64" align="right">6%</td> <td class="xl66" style="width: 48pt;" width="64" align="right">7%</td> <td class="xl66" style="width: 48pt;" width="64" align="right">8%</td> <td class="xl66" style="width: 48pt;" width="64" align="right">9%</td> <td class="xl66" style="width: 48pt;" width="64" align="right">10%</td> <td class="xl66" style="width: 48pt;" width="64" align="right">11%</td> <td class="xl66" style="width: 48pt;" width="64" align="right">12%</td> <td class="xl66" style="width: 48pt;" width="64" align="right">13%</td> <td class="xl66" style="width: 48pt;" width="64" align="right">14%</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">4.00</td> <td class="xl65" align="right">14.50</td> <td class="xl65" align="right">9.84</td> <td class="xl65" align="right">5.19</td> <td class="xl65" align="right">4.47</td> <td class="xl65" align="right">3.75</td> <td class="xl65" align="right">4.01</td> <td class="xl65" align="right">4.27</td> <td class="xl65" align="right">3.36</td> <td class="xl65" align="right">2.44</td> <td class="xl65" align="right">2.25</td> <td class="xl65" align="right">2.06</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">4.25</td> <td class="xl65" align="right">14.86</td> <td class="xl65" align="right">10.40</td> <td class="xl65" align="right">5.94</td> <td class="xl65" align="right">5.15</td> <td class="xl65" align="right">4.37</td> <td class="xl65" align="right">4.49</td> <td class="xl65" align="right">4.61</td> <td class="xl65" align="right">3.73</td> <td class="xl65" align="right">2.86</td> <td class="xl65" align="right">2.64</td> <td class="xl65" align="right">2.42</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">4.50</td> <td class="xl65" align="right">15.23</td> <td class="xl65" align="right">10.96</td> <td class="xl65" align="right">6.69</td> <td class="xl65" align="right">5.84</td> <td class="xl65" align="right">4.99</td> <td class="xl65" align="right">4.97</td> <td class="xl65" align="right">4.95</td> <td class="xl65" align="right">4.11</td> <td class="xl65" align="right">3.27</td> <td class="xl65" align="right">3.03</td> <td class="xl65" align="right">2.78</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">4.75</td> <td class="xl65" align="right">15.59</td> <td class="xl65" align="right">11.51</td> <td class="xl65" align="right">7.44</td> <td class="xl65" align="right">6.53</td> <td class="xl65" align="right">5.61</td> <td class="xl65" align="right">5.45</td> <td class="xl65" align="right">5.29</td> <td class="xl65" align="right">4.49</td> <td class="xl65" align="right">3.69</td> <td class="xl65" align="right">3.41</td> <td class="xl65" align="right">3.14</td> <td>
</td> </tr> </tbody></table>
 
Upvote 0

J Ericson

Board Regular
Joined
Jan 31, 2009
Messages
51
This one caught my eye, and I came up with this;

=INDEX(SpanCatForce,MATCH(B20,E1:E5,0),MATCH(C20,E1:P1,0))

I tried to match the ranges and cells you are using in your sheet, but fell short in a couple places. In this equation E1:E5 is the range of row values in your 'SpanCatForce' table (4, 4.25, 4.5, 4.75) and E1:P1 is the range of column values in your 'SpanCatForce' table (4%, 5%, 6%, etc). You'll have to change these accordingly.

HTH,
J Ericson
 
Upvote 0

Bunnygum

New Member
Joined
Mar 5, 2009
Messages
5
The value being returned is not correct. It is returning the value from the cell adjacent to the intersecting row and column.

Any hints on how to fix this would be gratefully appreciated.
 
Upvote 0

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
=INDEX(SpanCatForce,MATCH(B20,E1:E5,0),MATCH(C20,E1:P1,0))

Red is the row.

Blue is the column.

Add or subtract 1 where needed to adjust.
 
Upvote 0

Bunnygum

New Member
Joined
Mar 5, 2009
Messages
5
The issue with the offset has been solved. The problem was caused by the table SpanCatForce not including the label column.

Thanks to all who helped solving this.
 
Upvote 0

Forum statistics

Threads
1,191,043
Messages
5,984,298
Members
439,882
Latest member
gerdc

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
Top