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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
You are welcome!
Glad to help.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top