Lookup Assistance

[vandango05]

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.

 

[Robert Mika]

What about changing your table to

0</SPAN>	VR011N</SPAN>
2600</SPAN>	VR2011</SPAN>
3300</SPAN>	VR2011-AR</SPAN>
4000</SPAN>	VR2011-SR</SPAN>

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

 

[vandango05]

What about changing your table to

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.

Thank you for the reply.

 

[Skybluekid]

Hi,

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

 

[Robert Mika]

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>

Spreadsheet Formulas

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

 

[Aladin Akyurek]

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),"")