Lookup Assistance

vandango05

Board Regular
Hi.

I've got a table of sizes which results in an answer based on the size in cell B4 and are trying a VLOOKUP to give a result.

The table is structured like this in cells A13: B16

 2600 VR011N 3300 VR2011 4000 VR2011-AR 4500 VR2011-SR

<tbody>
</tbody>

My formula is: =VLOOKUP(B4,Sheet2!A13:B16,2,TRUE)

The issue I have is that if the size is over 2600mm the answer should be VR2011, however it gives an answer back as VR011N. Similally the other dimenions 3301mm would result VR2011 not VR2011-AR etc.

Can anyone help?

Thank you.

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
 0 VR011N 2600 VR2011 3300 VR2011-AR 4000 VR2011-SR

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

 0 VR011N 2600 VR2011 3300 VR2011-AR 4000 VR2011-SR

<tbody>
</tbody>

Yes I had thought of this option after posting and have changed it, but wondering if there was an alternate.

Hi,

This in another option =INDEX(A13:B16,MATCH(B4,A13:A16),2)

Hi,

This in another option =INDEX(A13:B16,MATCH(B4,A13:A16),2)
This will fail for value below 2600
Sheet1

 * A B C D 3 * 100 * #N/A 4 * 2599 * #N/A 5 * 2600 * VR011N 6 * 3300 * VR2011 7 * * * * 8 * * * * 9 * * * * 10 * * * * 11 * * * * 12 * * * * 13 2600 VR011N * * 14 3300 VR2011 * * 15 4000 VR2011-AR * * 16 4500 VR2011-SR * *

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

 Cell Formula D3 =INDEX(\$A\$13:\$B\$16,MATCH(B3,\$A\$13:\$A\$16),2) D4 =INDEX(\$A\$13:\$B\$16,MATCH(B4,\$A\$13:\$A\$16),2) D5 =INDEX(\$A\$13:\$B\$16,MATCH(B5,\$A\$13:\$A\$16),2) D6 =INDEX(\$A\$13:\$B\$16,MATCH(B6,\$A\$13:\$A\$16),2)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi.

I've got a table of sizes which results in an answer based on the size in cell B4 and are trying a VLOOKUP to give a result.

The table is structured like this in cells A13: B16

 2600 VR011N 3300 VR2011 4000 VR2011-AR 4500 VR2011-SR

<tbody>
</tbody>

My formula is: =VLOOKUP(B4,Sheet2!A13:B16,2,TRUE)

The issue I have is that if the size is over 2600mm the answer should be VR2011, however it gives an answer back as VR011N. Similally the other dimenions 3301mm would result VR2011 not VR2011-AR etc.

Can anyone help?

Thank you.

You could try something like:

=IF(\$B4>=MIN(Sheet2!\$A\$13:\$A\$16),LOOKUP(\$B4,Sheet2!\$A\$13:\$A\$16,Sheet2!\$B\$13:\$B\$16),"")

Replies
1
Views
279
Replies
0
Views
476
Replies
2
Views
489
Replies
0
Views
131
Replies
5
Views
6K

1,203,378
Messages
6,055,092
Members
444,761
Latest member
lneuberger

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.

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