IF statement w/ Vlookup Help

usernamenottaken

New Member
Joined
Apr 17, 2018
Messages
5
have worksheet consisting of thousands of names & address (Qrylic)
have separate worksheet consisting of a few hundred specific zip codes in a single list

Current
=IF(QryLic!G2=VLOOKUP(QryLic!G2,Sheet1!A:B,2,TRUE),QryLic!A2:M2,"$")

In perfect world:
Formula (which will be used to populate a new "worksheet") would look at cell "G2" (which is a zip code) on worksheet "Qrylic" (which is a list of names with addresses)
and
IF the zip code (G2) was listed/found/matched a simple list/table of zip codes on worksheet "Sheet1" (which is literately just a column of zip codes)
then
the result would be to display/= all the info listed for row 2 of "Qrylic" worksheet
(i.e. show the complete full name and address )
if
zip code not found then display $


Resulting in all coming back as $, which is not correct.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,486
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

I think you may be overcomplicating this. Note that VLOOKUP can only return the data from a single column, it cannot return multiple columns at once.

Try this:
Code:
[COLOR=#333333]=IFERROR(VLOOKUP(QryLic!G2,Sheet1!A:M,2,TRUE),"$")
[/COLOR]
That will return the value from column B of your matching row.
To return the other columns, just add more VLOOKUPs, i.e.
for the 3rd column:
Code:
[COLOR=#333333]=IFERROR(VLOOKUP(QryLic!G2,Sheet1!A:M,[/COLOR][COLOR=#ff0000][B]3[/B][/COLOR][COLOR=#333333],TRUE),"$")
[/COLOR]
etc
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,486
Office Version
  1. 365
Platform
  1. Windows
So, here is one way to do it.

Assuming QryLic sheet has your thousands of addresses, and Zip Code is in column G.
And the list of Zip Code for records you want to keep is on Sheet1, column A.
Then pick some blank column on QryLic sheet (let's say it is column N).
Then in cell N2, enter this equation:
Code:
=COUNTIF(Sheet1!A:A,QryLic!G2)
and copy down for all rows.
Then use Filters and filter out the rows that have 0 in column N (or you can sort and delete, if you want to permanently remove them instead of just hiding them).
 

usernamenottaken

New Member
Joined
Apr 17, 2018
Messages
5

ADVERTISEMENT

Welcome to the Board!

I think you may be overcomplicating this. Note that VLOOKUP can only return the data from a single column, it cannot return multiple columns at once.

Try this:
Code:
[COLOR=#333333]=IFERROR(VLOOKUP(QryLic!G2,Sheet1!A:M,2,TRUE),"$")
[/COLOR]
That will return the value from column B of your matching row.
To return the other columns, just add more VLOOKUPs, i.e.
for the 3rd column:
Code:
[COLOR=#333333]=IFERROR(VLOOKUP(QryLic!G2,Sheet1!A:M,[/COLOR][COLOR=#ff0000][B]3[/B][/COLOR][COLOR=#333333],TRUE),"$")
[/COLOR]
etc


Worksheet "qry" is where all the info is (each row is a single name & address spread across several columns)........the verification if you will list of zip codes are on work"sheet1" (across just two columns A (index) & B (zip code#)

i pasted your suggested formula and the result was just the last entry of zip code verification list (sheet1)

my goal is simply to basically filter this list of addresses that covers a thousand zip codes down to a list of these addresses that only have a select 200 zip codes
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,486
Office Version
  1. 365
Platform
  1. Windows
Worksheet "qry" is where all the info is (each row is a single name & address spread across several columns)........the verification if you will list of zip codes are on work"sheet1" (across just two columns A (index) & B (zip code#)

i pasted your suggested formula and the result was just the last entry of zip code verification list (sheet1)

my goal is simply to basically filter this list of addresses that covers a thousand zip codes down to a list of these addresses that only have a select 200 zip codes
Look at my last post. Note if Zip Code is in column B, not A, you will need to change that in the formula:
Code:
=COUNTIF(Sheet1![COLOR=#ff0000]B:B[/COLOR],QryLic!G2)
 

usernamenottaken

New Member
Joined
Apr 17, 2018
Messages
5
So, here is one way to do it.

Assuming QryLic sheet has your thousands of addresses, and Zip Code is in column G.
And the list of Zip Code for records you want to keep is on Sheet1, column A.
Then pick some blank column on QryLic sheet (let's say it is column N).
Then in cell N2, enter this equation:
Code:
=COUNTIF(Sheet1!A:A,QryLic!G2)
and copy down for all rows.
Then use Filters and filter out the rows that have 0 in column N (or you can sort and delete, if you want to permanently remove them instead of just hiding them).


this will work (little scary that column "N" just happens to be the first blank column in my data.....)

i am almost positive this is how i did this about 1 year ago or so (couldn't remember, obviously)

thank you very much.....

and much simpler than the path i was currently on.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,486
Office Version
  1. 365
Platform
  1. Windows
You are welcome!
Glad to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,248
Messages
5,527,629
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top