Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108083 > unrolled thread

IF statement with multiple VLOOKUPs

Started bydakota park <coda281@gmail.com>
First post2015-10-09 08:29 -0700
Last post2015-10-09 15:08 -0700
Articles 10 — 3 participants

Back to article view | Back to microsoft.public.excel.programming

Contents

  IF statement with multiple VLOOKUPs dakota park <coda281@gmail.com> - 2015-10-09 08:29 -0700
    Re: IF statement with multiple VLOOKUPs "Peter T" <askformy@gmail.com> - 2015-10-09 17:33 +0100
      Re: IF statement with multiple VLOOKUPs dakota park <coda281@gmail.com> - 2015-10-09 10:11 -0700
    Re: IF statement with multiple VLOOKUPs Claus Busch <claus_busch@t-online.de> - 2015-10-09 18:35 +0200
      Re: IF statement with multiple VLOOKUPs Claus Busch <claus_busch@t-online.de> - 2015-10-09 18:46 +0200
        Re: IF statement with multiple VLOOKUPs dakota park <coda281@gmail.com> - 2015-10-09 10:00 -0700
          Re: IF statement with multiple VLOOKUPs Claus Busch <claus_busch@t-online.de> - 2015-10-09 19:20 +0200
            Re: IF statement with multiple VLOOKUPs dakota park <coda281@gmail.com> - 2015-10-09 10:37 -0700
              Re: IF statement with multiple VLOOKUPs Claus Busch <claus_busch@t-online.de> - 2015-10-09 19:54 +0200
                Re: IF statement with multiple VLOOKUPs dakota park <coda281@gmail.com> - 2015-10-09 15:08 -0700

#108083 — IF statement with multiple VLOOKUPs

Fromdakota park <coda281@gmail.com>
Date2015-10-09 08:29 -0700
SubjectIF statement with multiple VLOOKUPs
Message-ID<8a7c7353-089e-43f9-a684-77ffb82dabe5@googlegroups.com>
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,'Categories (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,'Categories (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!

[toc] | [next] | [standalone]

#108085

From"Peter T" <askformy@gmail.com>
Date2015-10-09 17:33 +0100
Message-ID<mv8pof$snv$1@dont-email.me>
In reply to#108083
If I follow, adapt your formula like this

=If(IsError(mylookup1)=FALSE, "MODERATE",
  If(IsError(mylookup2)=FALSE, "UPPER",
  If(IsError(...etc

If you flip the True/False arg's you could dispense with the respective 
=FALSE, though maybe harder to read

Regards,
Peter T


"dakota park" <coda281@gmail.com> wrote in message 
news:8a7c7353-089e-43f9-a684-77ffb82dabe5@googlegroups.com...
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,'Categories 
(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,'Categories 
(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!

[toc] | [prev] | [next] | [standalone]

#108090

Fromdakota park <coda281@gmail.com>
Date2015-10-09 10:11 -0700
Message-ID<89c108c3-4a3b-4c57-a428-ea2c55a66067@googlegroups.com>
In reply to#108085
On Friday, October 9, 2015 at 11:26:57 AM UTC-5, Peter T wrote:
> If I follow, adapt your formula like this
> 
> =If(IsError(mylookup1)=FALSE, "MODERATE",
>   If(IsError(mylookup2)=FALSE, "UPPER",
>   If(IsError(...etc
> 
> If you flip the True/False arg's you could dispense with the respective 
> =FALSE, though maybe harder to read
> 
> Regards,
> Peter T
> 
> 
> "dakota park" wrote in message 
> news:8a7c7353-089e-43f9-a684-77ffb82...
> 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,'Categories 
> (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,'Categories 
> (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!

Thanks Peter! This formula also worked! I appreciate the help, and hope to continue to figure out all of these formulas and how to work!

[toc] | [prev] | [next] | [standalone]

#108086

FromClaus Busch <claus_busch@t-online.de>
Date2015-10-09 18:35 +0200
Message-ID<mv8q9d$uub$1@dont-email.me>
In reply to#108083
Hi,

Am Fri, 9 Oct 2015 08:29:40 -0700 (PDT) schrieb dakota park:

> 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.

try:

=INDEX('Categories (2)'!A:E,1,MATCH(B2,INDEX('Categories (2)'!A:E,MIN(IF('Categories (2)'!A2:E100=B2,ROW(2:100))),0),0))

and enter the formula with CTRL+Shift+Enter


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

[toc] | [prev] | [next] | [standalone]

#108088

FromClaus Busch <claus_busch@t-online.de>
Date2015-10-09 18:46 +0200
Message-ID<mv8qtn$1lr$1@dont-email.me>
In reply to#108086
Hi Dakota,

Am Fri, 9 Oct 2015 18:35:46 +0200 schrieb Claus Busch:

> =INDEX('Categories (2)'!A:E,1,MATCH(B2,INDEX('Categories (2)'!A:E,MIN(IF('Categories (2)'!A2:E100=B2,ROW(2:100))),0),0))

sorry, forgot to handle an error:

=IFERROR(INDEX('Categories (2)'!A:E,1,MATCH(B2,INDEX('Categories (2)'!A:E,MIN(IF('Categories (2)'!A2:E100=B2,ROW(2:100))),0),0)),"Not Found")
and enter the formula with CRTL+Shift+Enter


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

[toc] | [prev] | [next] | [standalone]

#108089

Fromdakota park <coda281@gmail.com>
Date2015-10-09 10:00 -0700
Message-ID<ac522721-a2be-4431-b410-78bae6562aeb@googlegroups.com>
In reply to#108088
On Friday, October 9, 2015 at 11:46:51 AM UTC-5, Claus Busch wrote:
> Hi Dakota,
> 
> Am Fri, 9 Oct 2015 18:35:46 +0200 schrieb Claus Busch:
> 
> > =INDEX('Categories (2)'!A:E,1,MATCH(B2,INDEX('Categories (2)'!A:E,MIN(IF('Categories (2)'!A2:E100=B2,ROW(2:100))),0),0))
> 
> sorry, forgot to handle an error:
> 
> =IFERROR(INDEX('Categories (2)'!A:E,1,MATCH(B2,INDEX('Categories (2)'!A:E,MIN(IF('Categories (2)'!A2:E100=B2,ROW(2:100))),0),0)),"Not Found")
> and enter the formula with CRTL+Shift+Enter
> 
> 
> Regards
> Claus B.
> -- 
> Vista Ultimate / Windows7
> Office 2007 Ultimate / 2010 Professional

Awesome, this works! Just a couple of $ signs (sorry not sure what the excel terminology is still and it is perfect!

While I was still playing around, I came up with this super-iferror statement that worked as well, but looks kinda funky:

=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"))),"")

Thank you so much Claus!

[toc] | [prev] | [next] | [standalone]

#108091

FromClaus Busch <claus_busch@t-online.de>
Date2015-10-09 19:20 +0200
Message-ID<mv8ssl$a49$1@dont-email.me>
In reply to#108089
Hi Dakota,

Am Fri, 9 Oct 2015 10:00:51 -0700 (PDT) schrieb dakota park:

> Awesome, this works! Just a couple of $ signs (sorry not sure what the excel terminology is still and it is perfect!

you are welcome.

Here is another solution. A little bit shorter and without
CTRL+Shift+Enter:

=IFERROR(INDEX('Categories (2)'!$A$1:$E$1,1,SUMPRODUCT(('Categories (2)'!$A$1:$E$200=B2)*COLUMN(A:E))),"Not Found")


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

[toc] | [prev] | [next] | [standalone]

#108093

Fromdakota park <coda281@gmail.com>
Date2015-10-09 10:37 -0700
Message-ID<dd46d6c7-2aad-4c84-bdeb-aeca3418a19b@googlegroups.com>
In reply to#108091
> Here is another solution. A little bit shorter and without
> CTRL+Shift+Enter:
> 
> =IFERROR(INDEX('Categories (2)'!$A$1:$E$1,1,SUMPRODUCT(('Categories (2)'!$A$1:$E$200=B2)*COLUMN(A:E))),"Not Found")


This one worked for giving me all the outputs except where the iferror should return a "Not Found," I am getting back "UNKNOWN." I am trying to understand that last one. Thanks

[toc] | [prev] | [next] | [standalone]

#108094

FromClaus Busch <claus_busch@t-online.de>
Date2015-10-09 19:54 +0200
Message-ID<mv8uti$ii9$1@dont-email.me>
In reply to#108093
Hi,

Am Fri, 9 Oct 2015 10:37:24 -0700 (PDT) schrieb dakota park:

> This one worked for giving me all the outputs except where the iferror should return a "Not Found," I am getting back "UNKNOWN." I am trying to understand that last one. Thanks

please look here:
https://onedrive.live.com/redir?resid=9378aab6121822a3!326&authkey=!AApmk7UTBA7IuAM&ithint=folder%2cxlsm
for "SearchGlobal"


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

[toc] | [prev] | [next] | [standalone]

#108095

Fromdakota park <coda281@gmail.com>
Date2015-10-09 15:08 -0700
Message-ID<5ecac8d7-d32f-4c8b-93e4-a0e4e9fb0e67@googlegroups.com>
In reply to#108094
My formula was place in column D on a separate worksheet. The part of the last formula you shared, where the iferror should kick in and return a "not found" was returning whatever classification was used for the D column on the separate worksheet (tab). I tested this out on your search global formula you used for column f. I relocated the formula over to columns e, d, and c to test it out. Same result.

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web