I have a list of numbers (ex: 1,3,5,7,9,12,13,14,15,16, etc). I need the
cell to look in another cell of a column and find one of the numbers. If it
finds it, I need it return a certain text value and if the # is not found, I
need it to return a different text value. The IF statement would be perfect
here, but my understanding is that no more than 7 IF statements can be
nested and I have more than 7. I researched the VLookup, but I don't see how
to get it to return the text values if the # is or is not found as opposed to
returning the "N/A" message. Help.

thanks

Hi JazzyLady,

Will the formula return the same value for any number found? If so, how about:

=IF(OR(A1=$C$1:$C$20),"Found","Not Found")

Where your list of values to search for is in cells C1:C20 and A1 is the
cell you want to say Found or Not Found or whatever your text response should
be.

This formula needs to be "array entered". Hold Control-Shift then hit Enter.

Does that help?

"LilJazzyLady" wrote:

I have a list of numbers (ex: 1,3,5,7,9,12,13,14,15,16, etc). I need the
cell to look in another cell of a column and find one of the numbers. If it
finds it, I need it return a certain text value and if the # is not found, I
need it to return a different text value. The IF statement would be perfect
here, but my understanding is that no more than 7 IF statements can be
nested and I have more than 7. I researched the VLookup, but I don't see how
to get it to return the text values if the # is or is not found as opposed to
returning the "N/A" message. Help.

thanks

assuming you have a table with your list of numbers and respective texts
try something like =IF(ISNA(VLOOKUP(A1,C1:D3,2,FALSE)),"not found
text",VLOOKUP(A1,C1:D3,2,FALSE))
substitute your table range or name for c1:d3,and A1 is the cell where you
are looking at the number
--
paul

remove nospam for email addy!



"LilJazzyLady" wrote:

I have a list of numbers (ex: 1,3,5,7,9,12,13,14,15,16, etc). I need the
cell to look in another cell of a column and find one of the numbers. If it
finds it, I need it return a certain text value and if the # is not found, I
need it to return a different text value. The IF statement would be perfect
here, but my understanding is that no more than 7 IF statements can be
nested and I have more than 7. I researched the VLookup, but I don't see how
to get it to return the text values if the # is or is not found as opposed to
returning the "N/A" message. Help.

thanks

Well I read the replies (Thanks by the way) but I'm not sure if that is
exactly what I need. Perhaps my post wasn't very clear and keep in mind I'm
not an Excel guru
:) I want the formula to look in B2 and if the # for that cell is a 1, I
want it to say Blue, if it finds a 2 "Red", 3 "Yellow" and so forth...

"LilJazzyLady" wrote:

I have a list of numbers (ex: 1,3,5,7,9,12,13,14,15,16, etc). I need the
cell to look in another cell of a column and find one of the numbers. If it
finds it, I need it return a certain text value and if the # is not found, I
need it to return a different text value. The IF statement would be perfect
here, but my understanding is that no more than 7 IF statements can be
nested and I have more than 7. I researched the VLookup, but I don't see how
to get it to return the text values if the # is or is not found as opposed to
returning the "N/A" message. Help.

thanks

I want the formula to look in B2 and if the # for that cell is a 1,
I want it to say Blue, if it finds a 2 "Red", 3 "Yellow" and so
forth...

Maybe the csv file below would help.

You can save the part below "cut here" in a file
"a.csv"
(including the quotes) and double-click on the file to open it in Excel.

------------ cut here ------------
"=IF(COUNTIF(C:C,B1)=0,""Missing"",VLOOKUP(B1,C:D, 2,FALSE))",8,,
,,1,Blue
,,3,Red
,,5,Yellow
,,7,Green
,,9,Orange
,,12,Cyan
,,13,Magenta
,,14,Brown
,,15,White
,,16,Gray

Hi JazzyLady,

Oooh.

If I'm understanding correctly this time :-) , then you'll want to use
VLookup with a lookup table.

Here's how.

In a separate spot on the spreadsheet, or on another sheet, create your list
of values. For example:

H I
1 1 Blue
2 2 Red
3 3 Yellow

H1 = 1, I1 = Blue, H2 = 2, I2 = Red, etc etc

So your formula would look like: =VLOOKUP(B2,H1:I3,2,FALSE)

Where B2 is where the values you want to evaluate are
Where H1:I3 is your list of values and the related colors
FYI - the 2 after H1:I3 in the formula says get the value from the second
column (or color names in this case). The false means give an exact match
only.

Does that help?



"LilJazzyLady" wrote:

Well I read the replies (Thanks by the way) but I'm not sure if that is
exactly what I need. Perhaps my post wasn't very clear and keep in mind I'm
not an Excel guru
:) I want the formula to look in B2 and if the # for that cell is a 1, I
want it to say Blue, if it finds a 2 "Red", 3 "Yellow" and so forth...

"LilJazzyLady" wrote:

I have a list of numbers (ex: 1,3,5,7,9,12,13,14,15,16, etc). I need the
cell to look in another cell of a column and find one of the numbers. If it
finds it, I need it return a certain text value and if the # is not found, I
need it to return a different text value. The IF statement would be perfect
here, but my understanding is that no more than 7 IF statements can be
nested and I have more than 7. I researched the VLookup, but I don't see how
to get it to return the text values if the # is or is not found as opposed to
returning the "N/A" message. Help.

thanks

JazzyLady,

Just re-read your original post. You will need to change the formula to:

=if(isna(VLOOKUP(B2,H1:I3,2,FALSE)),"",VLOOKUP(B2, H1:I3,2,FALSE))

That will leave the cell with a "" (looks blank) if the number is not found
in your list. If you would like the cell to say something in particular if
the number is not found, the substitute that text for the "". (e.g., "not
found").

Does that help?



"Dominic" wrote:

Hi JazzyLady,

Oooh.

If I'm understanding correctly this time :-) , then you'll want to use
VLookup with a lookup table.

Here's how.

In a separate spot on the spreadsheet, or on another sheet, create your list
of values. For example:

H I
1 1 Blue
2 2 Red
3 3 Yellow

H1 = 1, I1 = Blue, H2 = 2, I2 = Red, etc etc

So your formula would look like: =VLOOKUP(B2,H1:I3,2,FALSE)

Where B2 is where the values you want to evaluate are
Where H1:I3 is your list of values and the related colors
FYI - the 2 after H1:I3 in the formula says get the value from the second
column (or color names in this case). The false means give an exact match
only.

Does that help?



"LilJazzyLady" wrote:

Well I read the replies (Thanks by the way) but I'm not sure if that is
exactly what I need. Perhaps my post wasn't very clear and keep in mind I'm
not an Excel guru
:) I want the formula to look in B2 and if the # for that cell is a 1, I
want it to say Blue, if it finds a 2 "Red", 3 "Yellow" and so forth...

"LilJazzyLady" wrote:

I have a list of numbers (ex: 1,3,5,7,9,12,13,14,15,16, etc). I need the
cell to look in another cell of a column and find one of the numbers. If it
finds it, I need it return a certain text value and if the # is not found, I
need it to return a different text value. The IF statement would be perfect
here, but my understanding is that no more than 7 IF statements can be
nested and I have more than 7. I researched the VLookup, but I don't see how
to get it to return the text values if the # is or is not found as opposed to
returning the "N/A" message. Help.

thanks