[Terry Pinnell[_4_]]

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

[Claus Busch]

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

[Claus Busch]

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

[Terry Pinnell[_4_]]

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

[GS[_6_]]

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

[GS[_6_]]

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

[pedro]

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.

[Terry Pinnell[_4_]]

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