1. ## Help on multiple conditions formula, please

I've been trying rack my brain on how to set up the syntax for inserting text into a cell depending on what another cell contains.

For instance... I have a series of code numbers (in a column) that I want to type in manually, (500...501... 502...etc). I would like each adjacent cell to automatically fill in with text that describes the code that was typed in. I would like the logic to follow this:

=if(a5= "500" then gasoline otherwise if a5 = "501" then groceries, otherwise if a5 = "502" then utilities otherwise "").

Thanks... Steve

2. Originally Posted by blackjeep22
I've been trying rack my brain on how to set up the syntax for inserting text into a cell depending on what another cell contains.

For instance... I have a series of code numbers (in a column) that I want to type in manually, (500...501... 502...etc). I would like each adjacent cell to automatically fill in with text that describes the code that was typed in. I would like the logic to follow this:

=if(a5= "500" then gasoline otherwise if a5 = "501" then groceries, otherwise if a5 = "502" then utilities otherwise "").

Thanks... Steve

Try: If(a5=500,"gasoline",if(a5=501,"groceries",if(a5=5 02,"utilities,"")))

3. If you have a longer list and run into nesting issues you can use:
=IF(ISNA(VLOOKUP(A5,{500,"gasoline";501,"groceries ";502,"utilites"},2,0)),"",VLOOKUP(A5,{500,"gasoli ne";501,"groceries";502,"utilites"},2,0))

Of course instead of building the arrays in the formula, you could create a datatable to look up the values...

Steve

4. If your variables are more than say a dozen entries, you should set up a section of you workshhet as a table then use VLOOKUP, as it is quicker and easier to maintain if more variable need to be added.

5. Thanks everyone... I've never used VLOOKUP... I'll look into it.

6. Here is an example of how VLOOKUP might be used.

HTH

7. An alternative is to use the choose function if the codes are all contiguous

=IF(A5>499 and A5 <503,CHOOSE(A5-499,"gasoline","groceries","utilities"),"")

8. you could use an in-book (or external workbook as well) range reference to check, somewhat a quick "DB" like list of codes and related items on a named range in a specified worksheet (perhaps in a specified workbook).
with col A having the code, (1st column) and col B (2nd) having the text you need in a range named "refRange" (which is sheet3!A1:B20 for example), use
=VLOOKUP(A1,refRange,2,FALSE) in top column B cell, and copy the formula down the B column (grab lower right corner when cursor turns to a "plus" sign, pull down across the rows). each row reference in the formula for each row's cell will be updated with A2, A3, ... A20.
yeppir, just like tfspry shows in the above attachment

9. You could use VBA to program/write a function to respond with what you want. I did this for an unavailable financial formula when I was trying to do a calculation for determining ROI that was different than what was available within the included functions in Excel.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•