If this then that equals

[Gazman1976]

Hi, can anybody help me with this formula that i can't work out please?

i have a drop down box in one column with 5 different sizes ie Extra Small, Small, Medium, Large and Extra Large and next to that l would like a number value to appear when a certain drop down is selected.

Ie

Extra Small would equal 32

Medium would equal 56

Extra Large equal 100

Any help with this would be great as l can't figure this out.

Thanks in advance

 

[Michael M]

Maybe this, but you will have to expand it for the other "Sizes", as you haven't provided the numbers for them

=LOOKUP(G2,{"Extra Small","Medium","Extra Large"},{32,56,100})

 

[Peter_SSs]

Maybe this, but you will have to expand it for the other "Sizes", as you haven't provided the numbers for them

=LOOKUP(G2,{"Extra Small","Medium","Extra Large"},{32,56,100})

Michael, did you try that formula with Extra Large in G2?

It would need to be

=LOOKUP(G2,{"Extra Large","Extra Small","Medium"},{100,32,56})

However, it appears that there may actually be a lookup table in B45:C49 in which case I would use

=VLOOKUP(G2,$B$45:$C$49,2,0)

 

[Gazman1976]

Maybe this, but you will have to expand it for the other "Sizes", as you haven't provided the numbers for them

=LOOKUP(G2,{"Extra Small","Medium","Extra Large"},{32,56,100})

This worked thank you. Just need some advice on how to remove the #N/A from column H after l drag down the formula.

 

[Michael M]

Change the formula to

=IF(G2="","",LOOKUP(G2,{"Extra Large","Extra Small","Medium",""},{100,32,56,""}))

 

[Peter_SSs]

This worked thank you.

As I mentioned before, it did not work if the lookup value was "Extra large" (return is #N/A) as the values in the first array must be in ascending order for it to work. You will note that in post #5 Michael has now rearranged those values in the first array per my suggestion in post #3.

If your #N/A values are coming because the lookup cell is empty, then you could also use this.

=IFNA(LOOKUP(G2,{"Extra Large","Extra Small","Medium"},{100,32,56}),"")