# IF statement w/ Vlookup Help

##### New Member
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

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

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

#### Mississippi Girl

##### Board Regular
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:
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

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

##### New Member

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

##### New Member

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

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)``

##### New Member
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.

You are welcome!

Replies
1
Views
64
Replies
1
Views
263
Replies
3
Views
22
Replies
4
Views
52
Replies
17
Views
150