VLookUp Table

[Louise]

Hi all

I have a worksheet and need to add a VLookUp table to it
but am struggling to get it to work properly. Basically,
I have a list of figures in column D of a worksheet and
depending on what the figure is, I need to put a letter
next to each one in Column C.

I have tried to create a LookUp table with the necessary
values, but it doesn't seem to be working properly.
Below is the criteria I need the LookUp table to consist
of and depending on the figure it is looking at in Column
C, I need either S, M etc. to be returned in Column D.

I have created a LookUp table but it isn't returning the
correct value.

Below 50,000 cm2 = S

Between 50,000 - 100,000 cm2 = M

Between 100,000 - 300,000 cm2 = L

Between 300,000 - 500,000 cm2 = XL

500,000 and above = 2XL

Hope this makes sense! Any urgent help would be
appreciated.

Thank you.

Louise

 

[Mike Lewis]

Build a table with these values:

0 S
50000 M
100000 L
300000 XL
500000 2XL

Then the formula:

=VLOOKUP(F18,D19:E23,2,TRUE)

where F18 is the value to loookup and the table is d19:e23

should work.

Good Luck

Mike

 

[Arvi Laanemets]

Hi

No table needed unless you'll modify ranges or markings in future. With
processed value in cell A1:
=CHOOSE(MATCH(A1,{0,50000,100000,300000,500000},1),"S","M","L","XL","XXL")

You can have the ranges and matches as a table too, p.e. in range
SizeList!A2:B6
S 0
M 50000
L 100000
XL 300000
XXL 500000

Now the formula will be
=CHOOSE(MATCH(A1,SizeList!B2:B6,1),"S","M","L","XL","XXL")
or
=CHOOSE(MATCH(A1,SizeList!B2:B6,1),SizeList!A2,SizeList!A3,SizeList!A4,SizeL
ist!A5,SizeList!A6)

 

[Louise]

Thanks very much for this. I have altered my LookUp
table to the one you suggested and it works fine.

Thanks again.

Louise