Help on multiple conditions formula, please

Topic

New Reply

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

Reply | Quote

Replies

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=502,”utilities,””)))

Reply | Quote

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,”gasoline”;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

Reply | Quote

:cheers: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.

Reply | Quote

Thanks everyone… I’ve never used VLOOKUP… I’ll look into it.

Reply | Quote

Here is an example of how VLOOKUP might be used.

HTH

Reply | Quote

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"),"")

Reply | Quote

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

Reply | Quote

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.

Reply | Quote