Alternative to "IF" formula?

[DavidB90]

Hi all,

I apologize in advance for my lack of excel knowledge, but was looking for an alternative solution for the "IF" function.

Currently I'm running a billing spreadsheet where I have codes for our products followed by a description. The code is something simple like ABC123, but the description has to be fairly extensive. I'm looking for a simpler way to have the description automatically populate once the code has been entered. I only know of using the "IF" function, but when I have 100 different codes with 100 different descriptions the formula would be incredibly long and I can have hundreds of line items in a sheet.

If the codes were all in order it would be simple and I would just autofill, but unfortunately it doesn't work out that way. Any thoughts would be appreciated.

Thank You

 

[Joe4]

Welcome to the Board!

One simple way would be to make a two column lookup table, where the first column has your code and the second has its associated description.
Then you can use a simple VLOOKUP function to return the value from the code, as show here: VLOOKUP Function

 

[DavidB90]

Welcome to the Board!

One simple way would be to make a two column lookup table, where the first column has your code and the second has its associated description.
Then you can use a simple VLOOKUP function to return the value from the code, as show here: VLOOKUP Function

You are amazing! Just did a quick trial run and this is exactly what I was looking for.

It's funny how simple that function is, but even after google searching for half an hour for an answer I had no luck. Thankful for experts like you that know exactly what I'm looking for.

Many Thanks!

 

[awoohaw]

when using VLOOKUP you must not move insert any intervening columns and always have the lookup column on the left. With 2 columns only this is fairly benign.
But, one way to get the same results is with index/match formulas. Assume your lookup value is in cell A1, the lookup references are in D2:D50 and the lookup return is in E2:E50. An INDEX Match Formula would be:

=INDEX($E$2:$E$50,match(A1,$D$2:$D$50))

you can move the lookup and return ranges anywhere and the formula will not break.