Display Hole Size Based on Number of Wires

[Modify_inc]

I'm wanting to display a size in cell M24, such as 3/8", based on the number value in cell L24.

So say cell L24 <=2, then M24 = 3/8"

I know I could use a If Statement but I have 5 sizes to choose from, and may add more, which would make a cumbersome If formula.

I was hoping there may be a way to add them like a database or similar, where they can be called from, maybe from a separate column.

These are the conditions:

L24 <=5 then M24 = 1/2"
L24 <=7 then M24 = 9/16"
L24 <=12 then M24 = 3/4"
L24 <=19 then M24 = 1"

Is the If Statement my only option in Excel for doing something like this?

[Metoo7]

M24 formula
=LOOKUP(L24,{0,2,5,7,12}+0.01,{"3/8","1/2","9/16","3/4","1"}&"""")

[mazan2010]

Dear Modify_inc
please check attached file with four formulas that can be used as solution for your inquiry
one is If function

Please Login or Register  to view this content.

another is Chosen function

Please Login or Register  to view this content.

and also you use LOOKUP

Please Login or Register  to view this content.

and VLOOKUP

[Modify_inc]

Thank you for all the suggestions! I will experiment with each and hopefully learn more about them in the process.

[mazan2010]

If this solution is okay for you, will be thankful if you mark thread as solved from menu

[Modify_inc]

Dear Modify_inc
please check attached file with four formulas that can be used as solution for your inquiry
one is If function

Please Login or Register  to view this content.

another is Chosen function

Please Login or Register  to view this content.

and also you use LOOKUP

Please Login or Register  to view this content.

and VLOOKUP

Thanks for providing different ways to accomplish this. I'm still trying to figure out how your Lookup function is working. Even after reading more about the Lookup function I still can't make sense of how you did it.

Note a big deal, but the Vlookup doesn't work ideally, since a non-exact match will cause the Vlookup function to match the nearest value in the table that is still LESS than value. And in my case, I need the nearest value that is MORE than the value. So if there are 6 wires, it should display 9/16", instead it still shows 1/2".

[mazan2010]

Regarding LOOKUP formula how it works
in attached example

In column A (cell A2:A5) contain Smaller Value and Column B (cell B2:B5) is Larger

In cell G1 is the given value which you want to get its corresponding value, C2:C5 is the column data which you want to extract from.

I used Inch in formula as range name you see from formula in toolbar then name manager or you can replace Inch in formula with range ($C$2:$C$5)

As match value meaning True and NON-Match meaning False
True =1
false =0
so in attached example you will see 1 is divided by array that will give #N/A in case now any match