Trying to find the right formula/function

Microsoft Excel 2007 home and student
March 8, 2010 at 07:01:12
Specs: Windows 7

I am trying to find the correct formula or function for something I am trying to do in Excel 2007.

I have six three-digit binary codes (which have been generated from formulas I have already put in the Worksheet). I want certain text to appear in a specific cell depending on which of these three-digit codes are in another cell.

For example if cell BZ16 has "001" in it, I want cell C29 to display "Excellent".
Below are the six codes and what text I would like displayed depending on which code is present.

001 --> Excellent
011 --> Good
010 or 111 --> Moderate
110 --> Fair
100 --> Poor

I have tried a complicated and confusing IF function but have not been successful.

Any help you could provide would be greatly appreciated.

Thanks in advance,

See More: Trying to find the right formula/function

Report •

March 8, 2010 at 07:06:45
Use VLOOKUP. Read up on it in the Excel help files...

Build a table with your critera:

     A    B
1  001  Excellent
2  011  Good
3  010  Moderate
4  111  Moderate
5  110  Fair

With a three digit code in BZ16, use this in C29:


Report •

March 8, 2010 at 08:11:46
Thanks DerbyDad for your very prompt response!

I have read up on VLOOKUP function and tried to implement what you have written, but I am receiving a "#N/A" error.

(Since I have written my question I have changed my binary code a "2"s are in place where there were "0"s previously.)

Here's my table:

27 221 Excellent
28 211 Good
29 212 Moderate
30 111 Moderate
31 112 Fair
32 122 Poor

(Please note that BV is above the "221" and BW is above the "Excellent"...I counldnt get this post to format in that way.)

Here's the forumla I have typed in the appropriate cell:

Can you help steer when I went wrong?


Report •

March 8, 2010 at 09:57:35
Your formula and layout works for me.

Do you have a value in BZ16 that matches one of the values in BV27:BV32?

VLOOKUP returns a #N/A error if the lookup_value can not be found in the lookup_array.

Put a value in BZ16 that you think matches the value in BV27.

In any cell enter =BZ16=BV27. If that doesn't return TRUE, then the values don't really match.

If you don't get a TRUE, then I'd need more info to help you further, such as how the value gets placed in BZ16, etc.

Report •

Related Solutions

March 8, 2010 at 12:07:53
P.S. To avoid #N/A when a value can't be found, wrap the VLOOKUP in an IF statement:

=IF(ISNA(VLOOKUP(BZ16,$BV$27:$BW$32,2,0)),"Not Found",

This will attempt the VLOOKUP, but if the value isn't in the list, it will return Not Found instead of the error. If the value is found, then it will return whatever it is that VLOOKUP is supposed to return.

Note: I split the formula across 2 lines for ease of viewing. You will have to put it back together for it to work in Excel.

Report •

March 8, 2010 at 14:37:11

Following on from DerbyDad03's comments, look at how you are creating the 'binary' value - is it actually a text string.

If it is, it may not match.



When posting, to help line up columns, paste your response between <pre> and </pre> tags. They can be found above the reply box.


Report •

Ask Question