I really am rusty! Could someone please remind me how to enter a
function in col B that will return different strings depending on the
content of col A. Like this:

Col A Col B
----- -----
x This
y That
x This
x That
z Other
y That

It seems that the IF function has to have only two values, so cannot
be used for my purpose.

If it matters, I'm still using my ancient Excel 2000.



--
Terry, East Grinstead, UK

Hi Terry,

Am Mon, 08 Feb 2016 18:41:33 +0000 schrieb Terry Pinnell:

Col A Col B
----- -----
x This
y That
x This
x That
z Other
y That

It seems that the IF function has to have only two values, so cannot
be used for my purpose.

in your version you can nest a IR function 6 times.
Try:
=IF(A1="x","This",IF(A1="y","That","Other"))
or
=VLOOKUP(A1,{"x","This";"y","That";"z","Other"},2, 0)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Terry,

Am Mon, 8 Feb 2016 20:00:32 +0100 schrieb Claus Busch:

in your version you can nest a IR function 6 times.

sorry, two typos:
In the version before 2007 you can nest a IF function (and all other
functions) 7 times


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Claus Busch wrote:

Hi Terry,

Am Mon, 08 Feb 2016 18:41:33 +0000 schrieb Terry Pinnell:

Col A Col B
----- -----
x This
y That
x This
x That
z Other
y That

It seems that the IF function has to have only two values, so cannot
be used for my purpose.

in your version you can nest a IR function 6 times.
Try:
=IF(A1="x","This",IF(A1="y","That","Other"))
or
=VLOOKUP(A1,{"x","This";"y","That";"z","Other"},2 ,0)


Regards
Claus B.

Thanks for that characteristically fast reply, Claus!

Your first one works a treat - luckily I have exactly six options!

Haven't quite got my head around the VLOOKUP function yet. But I'll
experiment later, as it seems more versatile.

Best wishes,

--
Terry, East Grinstead, UK

On Mon, 08 Feb 2016 20:16:38 +0000, Terry Pinnell
wrote:

Haven't quite got my head around the VLOOKUP function yet. But I'll
experiment later, as it seems more versatile.

I use VLOOKUP/HLOOKUP a lot and they are great once you find the
handle.

Another suggestion, to obviate need for nested IFs...

Store all possible criteria in a local scope named range with their
respective return values, then use a lookup function.

Example:

DefinedName: "Sheet1!RetVals"
RefersTo: $A$1:$C$2

Layout:
X | Y | Z
This | That | Other

DefinedName: "Criteria"
RefersTo: $A3 (with B3 selected so it's col-absolute, row-relative)

You can hide these 2 rows so your data can be setup normal. Optionally,
you could use 2 cols (A:B).

Criteria in colA, return values in colB with this formula...

=HLOOKUP(RetVals,Criteria,2,False)

Now you can have however many Criteria/RetVal pairs as you like all
working with 1 simple formula.<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Oops! got the args switched around for the lookup.
See correction below...

Another suggestion, to obviate need for nested IFs...

Store all possible criteria in a local scope named range with their
respective return values, then use a lookup function.

Example:

DefinedName: "Sheet1!RetVals"
RefersTo: $A$1:$C$2

Layout:
X | Y | Z
This | That | Other

DefinedName: "Criteria"
RefersTo: $A3 (with B3 selected so it's col-absolute, row-relative)

You can hide these 2 rows so your data can be setup normal.
Optionally, you could use 2 cols (A:B).

Criteria in colA, return values in colB with this formula...


=HLOOKUP(Criteria,RetVals,2,False)


Now you can have however many Criteria/RetVal pairs as you like all
working with 1 simple formula.<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:

Oops! got the args switched around for the lookup.
See correction below...

Another suggestion, to obviate need for nested IFs...

Store all possible criteria in a local scope named range with their
respective return values, then use a lookup function.

Example:

DefinedName: "Sheet1!RetVals"
RefersTo: $A$1:$C$2

Layout:
X | Y | Z
This | That | Other

DefinedName: "Criteria"
RefersTo: $A3 (with B3 selected so it's col-absolute, row-relative)

You can hide these 2 rows so your data can be setup normal.
Optionally, you could use 2 cols (A:B).

Criteria in colA, return values in colB with this formula...


=HLOOKUP(Criteria,RetVals,2,False)


Now you can have however many Criteria/RetVal pairs as you like all
working with 1 simple formula.<g


Thanks Gary, I'll try that flexible method at next opportunity.

--
Terry, East Grinstead, UK