IF statement w/ Vlookup Help

[usernamenottaken]

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.

 

[Mississippi Girl]

What is your end goal? To ensure the zip code is valid?

 

[Joe4]

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:

[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:

[COLOR=#333333]=IFERROR(VLOOKUP(QryLic!G2,Sheet1!A:M,[/COLOR][COLOR=#ff0000][B]3[/B][/COLOR][COLOR=#333333],TRUE),"$")
[/COLOR]

etc

 

[usernamenottaken]

end goal is to basically filter thousands of address to those that only have specific zip codes

 

[usernamenottaken]

What is your end goal? To ensure the zip code is valid?

end goal is to basically filter thousands of address to those that only have specific zip codes

 

[Joe4]

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:

=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]

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:

[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:

[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]

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:

=COUNTIF(Sheet1![COLOR=#ff0000]B:B[/COLOR],QryLic!G2)

 

[usernamenottaken]

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:

=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]

You are welcome!
Glad to help.