Formula Question - HELP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I posted a similiar question before, but I don't think I was clear. Here's
what I'm trying to do:

In colum B, each corresponding cell has a number (ex: B2 has a 1, B3 has a
4, etc). In Column D is where I want to return the text value. I need the
formula to search the cell in Column B and if it finds a 1, to return the
value "Blue," a 2 to return "Red," a 4 to return "Yellow" and so forth. An
If statement doesn't work because I have more than 7 conditions. What else
can I do here?

Hope that's clear.

Thanks!!
 
I would suggest creating a table on a seperate sheet, then using the VLOOKUP
function to retrieve your values.

So, on Sheet2, you'd have a table like this:

ColA ColB
1 Blue
2 Red
3 Purple
4 Yellow
etc...

Back on Sheet1, use this formula in D2:

=VLOOKUP(B2,Sheet2!$A$1:$B$100,2,FALSE)

Copy the formula down column D as needed.

HTH,
Elkar
 
Hi:

In another part of the worksheet, create a list with 2 columns:

1 Blue
2 Red
3 Green
etc
and for preference have this range sorted on the first column

Give this range a name, eg colours

Then in each cell in column D type:
=vlookup(B1,colours,2,false)

(In this formula, B1 is the source value, colours is the range yo
created above, 2 means column 2 of that range, and false means that th
values in column 1 of the lookup table are not necessarily sorted)

This should return the appropriate colour to the cell.

Regards
Mik
 
Setup a table, 2 columns by n rows, in M1:N10 say, with values of
1 Blue
2 Red
4 Yellow
etc

Then use

=VLOOKUP(B2,$M$1:$N$10,2,False)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Back
Top