Using vlookup and if/then statements for multiple tables

Here is my problem:

In Column A, there are lists of elements. In Column B & C, there are values associated with each element and these two columns are two different categories (Category 1 and Category 2).

On a second sheet, I have a random generated sheet that contains elements in Column A that may or may not be on the first sheet and they have values in Column B. I have tried to create a huge formula in Column C that will look at the value associated with the elements on the 2nd sheet, then it will refer to the first sheet and if that value equals or exceeds the values in Columns B or C, it will either give the user one of three choices: "Category 1", "Category 2", or "Not NTS".

However, try as I might, I cannot get this formula to work right b/c my formula is not giving me the right answers based on my workbook example given. I also run into the problem that if the element in the second sheet cannot be found in the first sheet table, I get a "#N/A" and I would prefer it to say "Not NTS". I tried using the "ISNA" function, but it seems to be also screwing with my results...

I created a post before this one about this but I don't think it was as clear and so I created a second post that hopefully is more clear with a workbook example.

Hopefully someone can help me b/c I have been attempting to do this for almost 4 hours now...

HP RodNuclear

Hi,

One way. In C2

=IF(B2>INDEX('10CFR20_App_E_NTS'!B:B,MATCH(A2,'10CFR20_App_E_NTS'!A:A,FALSE),1),"Cat1",IF(B2>INDEX('10CFR20_App_E_NTS'!C:C,MATCH(A2,'10CFR20_App_E_NTS'!A:A,FALSE),1),"Cat2","Not NTS"))

Regards

Hi HP RodNuclear,

Strange problem. I think you can't have the name of a range the same as the worksheet. Either that or you have an invalid character in the named range or worksheet name.

I changed the Name Range to ElementTable and did a VLookup and it worked fine.

See if you have a naming problem.

Richard, your code worked perfectly except for one thing: if the element is NOT listed in the table, it gives me a "#N/A" and I'd prefer for it to say "Not NTS" as well, but other than that, it works wonderful. Thank you so much.

Also, b/c I am not familiar with index/match, I wanted to see why the vlookup isn't working. So MarvinP, I tried changing the table I used to 'ElementTable', but it still isn't working right. What else am I doing wrong???

Hi,

Since you have Excel 2010 you could wrap the whole formula in an =IFERROR() function.
i.e.

=IFERROR(original_formula,"Not NTS")

Regards

Look at http://www.exceltip.com/st/Index_For...rmula/700.html and
and then http://office.microsoft.com/en-us/ex...001154902.aspx

In general VLookup only works if you are looking at the column on the leftmost of your range.

Hi,

It's a bit messy but you need to wrap the formula as follows

=IF(ISERROR(original_formula),"Not NTS",original_formula)

Regards