# Vlookup multiple columns

#### Bunnygum

##### New Member
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
Because you seem to have a consistent pattern here, perhaps something like
Code:
``=vlookup(b20,spancatforce,(c20*100)-2,false)``

#### Ron Coderre

##### MrExcel MVP
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:

#### Bunnygum

##### New Member
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>

#### J Ericson

##### Board Regular
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

#### Bunnygum

##### New Member
That is almost working. It is return the value to the left of the cell.
Cheers
Peter

#### Bunnygum

##### New Member
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.

##### Well-known Member
=INDEX(SpanCatForce,MATCH(B20,E1:E5,0),MATCH(C20,E1:P1,0))

Red is the row.

Blue is the column.

#### Bunnygum

##### New Member
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.

Replies
5
Views
182
Replies
0
Views
247
Replies
27
Views
567
Replies
5
Views
228
Replies
1
Views
139

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.

### Which adblocker are you using?

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

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