Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Help with Multiple VLOOKUPs in an IF statement

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!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Help with Multiple VLOOKUPs in an IF statement

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"))),"")
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 56
Default Help with Multiple VLOOKUPs in an IF statement

On Friday, October 9, 2015 at 9:48:24 AM UTC-7, dakota park wrote:
is there an easier way to write this formula
=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"))),"")


The following is different. Whether or not it's easier is a matter of taste..
=IF(COUNTIF(Categories!A:A,B2)0,"LOW","")&
IF(COUNTIF(Categories!B:B,B2)0,"MIDDLE","")&
IF(COUNTIF(Categories!C:C,B2)0,"MODERATE","")&
IF(COUNTIF(Categories!D:D,B2)0,"UPPER","")&
IF(COUNTIF(Categories!E:E,B2)0,"UNKNOWN","")
For me, a chain of concatenations would be easier to test and to modify.

Hopefully, this still meets the objective.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Excel Formula (Multiple Vlookups in an If Statement" [email protected] Excel Discussion (Misc queries) 2 October 9th 15 05:50 PM
IF statement with multiple VLOOKUPs GijsKijlstra Excel Discussion (Misc queries) 4 May 30th 12 08:42 AM
vlookups and if statement excelrookie Excel Worksheet Functions 3 August 27th 08 03:20 AM
multiple vlookups in one statement Dave Excel Worksheet Functions 3 January 30th 08 07:56 PM
multiple vlookups inthestands Excel Worksheet Functions 1 January 12th 05 10:07 PM


All times are GMT +1. The time now is 02:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"