Vlookup multiple columns

[Bunnygum]

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

 

[Gerald Higgins]

Because you seem to have a consistent pattern here, perhaps something like

=vlookup(b20,spancatforce,(c20*100)-2,false)

 

[Ron Coderre]

Perhaps something like this:

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

Edited to include this less arcane approach:

=IF(AND(C20>=0.04,C20<=0.14),VLOOKUP(B20,SpanCatForce,INT(C20*100)-2),"CHECK")

Is that something you can work with?

 

[Bunnygum]

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]

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]

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

 

[Bunnygum]

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.

 

[Sal Paradise]

=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.

 

[Bunnygum]

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.