Not sure if I'm explaining this right but here it goes:

Trying to get an IF statement where if a certain set of data is entered into

a cell the resulting cell will have a certain output:

ie. If A1=Red then b1 would be 1 or if A1=Blue then b1 would be 2, etc.....

Hope someone can help on this!

Thanks,

Sean N

For nested IFs, in B1 insert the formula

=IF(A1="Red",1,IF(A1="Blue",2,"alternative result if neither red nor blue"))

If there are too many alternatives for the nesting limit in Excel (7 in

Excel 2003), then VLOOKUP may be the best bet.

=IF(A1="Red",1,IF(A1="Blue",2,"alternative result if neither red nor blue"))

If there are too many alternatives for the nesting limit in Excel (7 in

Excel 2003), then VLOOKUP may be the best bet.

Use VLOOKUP to simplify things

List all the associations/choices in cols A & B in Sheet2 (say), eg:

Red 1

Blue 2

etc

Then in any other sheet,

assuming the lookup values (Red, Blue) are in A2 down

you can use this in B2: =IF(A2="","",VLOOKUP(A2,Sheet2!A:B,2,0))

Copy B2 down as needed

--

Max

Singapore

http://www.yqcomputer.com/

Downloads:21,000 Files:370 Subscribers:66

xdemechanik

---

List all the associations/choices in cols A & B in Sheet2 (say), eg:

Red 1

Blue 2

etc

Then in any other sheet,

assuming the lookup values (Red, Blue) are in A2 down

you can use this in B2: =IF(A2="","",VLOOKUP(A2,Sheet2!A:B,2,0))

Copy B2 down as needed

--

Max

Singapore

http://www.yqcomputer.com/

Downloads:21,000 Files:370 Subscribers:66

xdemechanik

---

You can do the work in the formula without the VLOOKUP table somewhere

by showing the options _in_the_formula_itself_, too. CHOOSE is a good

option if the cell you're referencing is going to have a number in

ascending order...like 1, 2 or three.

A1 =CHOOSE(B1,\"RED\",\"BLUE\",\"GREEN\")

This would PUT the word RED in cell A1 if B1 had the number 1 in it.

==========

To go the other way, seeing "RED" and getting "1" in return, a LOOKUP

with the arrary IN the formula works:

*B1 =LOOKUP(A1,{"blue","green",**"red"**},{2,3,1})*

I think this is what you were originally asking...the order is weird

because it needs to be alphabetical.

--

JBeaucaire

------------------------------------------------------------------------

JBeaucaire's Profile: http://www.yqcomputer.com/

View this thread: http://www.yqcomputer.com/

Hi,

Personally I would usually use a lookup table because it means that if

things change, you can edit the table, a range in the spreadsheet, rather

than a series of formulas.

That said, if you want to store the results in the formula the order would

be more logical if you use VLOOKUP:

=VLOOKUP(A1,{"Red",1;"Blue",2;"Green",3},2,)

--

If this helps, please click the Yes button

Cheers,

Shane Devenshire

Personally I would usually use a lookup table because it means that if

things change, you can edit the table, a range in the spreadsheet, rather

than a series of formulas.

That said, if you want to store the results in the formula the order would

be more logical if you use VLOOKUP:

=VLOOKUP(A1,{"Red",1;"Blue",2;"Green",3},2,)

--

If this helps, please click the Yes button

Cheers,

Shane Devenshire

1. New IFS interface (jfs.ifs, udf.ifs for example)

3. differecnce between ${IFS} and "${IFS}"

4. IFs, ELSEs, END IFS -- proper construction

5. Why there is a need of IFS and non-IFS LSP sockets?

6. Small (JFS.IFS or HPFS.IFS AutoCheck) install bump, Solved!

7. What does IFS="${IFS}[]()" mean?

8. nested ifs and end ifs and elses

9. Editing and Moviing from 6 If's to 7 Ifs (Nested)

10. multiple ifs converted to case statement in vb function

11. multiple ifs. multiple fields, multiple criteria

12. multiple ifs, mutliple fields, multiple criteria

13. Why isn't the If's statement, after the expression, being executed?

14. what is the max no. of nested Ifs can an If Statements have in EXC

5 post • Page:**1** of **1**