I have a table with the top row headers (classifications) of "LOW," "MIDDLE," "MODERATE," "UPPER," & "UNKNOWN." Under each are random (identification) numbers (e.g. under "LOW" there may be 1103, 1105, 1107.1, 1108.2, etc.).

On the subject chart, I have a column of account numbers, a column of corresponding identification numbers, and then the classification. What I am having trouble building, is an if statement with a vlookup which will return the matching classification.

An example of the formula I could use to get a returning "MIDDLE" value if the identification matches that classification is this:

=IF(VLOOKUP(B2,'Categories (2)'!B:B,1,FALSE)=B2,"MIDDLE", "NOT FOUND")

B2 refers to the subject chart's identification numbers, and the categories worksheet column B is the column associated with the "MIDDLE" classification and the identification numbers below it.

I want to build an if statement that will return the classification and search all columns of the 1st chart.

This was my closest guess, but it only searches the "MIDDLE" column, and only returns a value if it is a "MIDDLE" match:

=IF(VLOOKUP(B2,'Categories (2)'!B:B,1,FALSE)=B2,"MIDDLE",IF(VLOOKUP(B2,'Categ ories (2)'!$D$1:$D$96,1,FALSE)=B2,"UPPER",IF(VLOOKUP(B2, 'Categories (2)'!$C$1:$C$55,1,FALSE)=B2,"MODERATE",IF(VLOOKUP( B2,'Categories (2)'!$A$1:$A$21,1,FALSE)=B2,"LOW",IF(VLOOKUP(B2,'C ategories (2)'!$E$1:$E$4,1,FALSE)=B2,"UNKNOWN","NOT FOUND")))))

For every "MIDDLE" match, it returns "MIDDLE," but for everything else, I receive back "#N/A."

Any help would be thoroughly appreciated! Thanks!

I ended up getting the following formula to work, but was still curious as to whether or not there is an easier way to write this formula - (excel noobie)

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IF(MATCH( B2,Categories!A:A,0)<"#N/A","LOW"),(IF(MATCH(B2,Categories!B:B,0)<"#N/A","MIDDLE"))),(IF(MATCH(B2,Categories!C:C,0)< "#N/A","MODERATE"))),(IF(MATCH(B2,Categories!D:D,0)<" #N/A","UPPER"))),(IF(MATCH(B2,Categories!E:E,0)<" #N/A","UNKNOWN"))),"")

Hi Dakota,

Am Fri, 9 Oct 2015 09:48:05 -0700 (PDT) schrieb dakota park:

I ended up getting the following formula to work, but was still curious as to whether or not there is an easier way to write this formula - (excel noobie)

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IF(MATCH( B2,Categories!A:A,0)<"#N/A","LOW"),(IF(MATCH(B2,Categories!B:B,0)<"#N/A","MIDDLE"))),(IF(MATCH(B2,Categories!C:C,0)< "#N/A","MODERATE"))),(IF(MATCH(B2,Categories!D:D,0)<" #N/A","UPPER"))),(IF(MATCH(B2,Categories!E:E,0)<" #N/A","UNKNOWN"))),"")

you have answers in group
microsoft.public.excel.programming

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional