• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 636
  • Last Modified:

Vlookup within an If statement Excel

Hi All,

Hoping one of you experts can help.

I have two excel worksheets that i need to compare data between then bring throgh one of the fields to the other sheets.  eg.

I require on sheet 1 values in A & C to check and match values A & D in Sheet 2
Sheet 1 A = Sheet 2 A
Sheet 1 C = Sheet 2 D.

If the test is correct i need the corresponding value in sheet 2 colum z to be brought to sheet 1 most likely in the form of a vlook.

hope the above is clear.

Regards

John,
0
jstlawyers
Asked:
jstlawyers
  • 2
  • 2
  • 2
1 Solution
 
gtglonerCommented:
Try this formula in Sheet 1 in cell D1 and copy down:

=IF(AND(A1=Sheet2!A1,Sheet1!C1=Sheet2!D1),Sheet2!Z1,"")
0
 
gtglonerCommented:
Here is an example I created in a file:
Book1.xls
0
 
barry houdiniCommented:
In sheet 1 D2 try this formula copied down
=LOOKUP(2,1/((A2=sheet2!A$2:A$100)*(C2=sheet2!D$2:D$100)),sheet2!Z$2:Z$100)
see attached
extend ranges as necessary
reagrds, barry

double-lookup.xls
0
WEBINAR: 10 Easy Ways to Lose a Password

Join us on June 27th at 8 am PDT to learn about the methods that hackers use to lift real, working credentials from even the most security-savvy employees. We'll cover the importance of multi-factor authentication and how these solutions can better protect your business!

 
jstlawyersAuthor Commented:
Thanks guys for the code.

both of you are 90% their and i've done something wrong (not 100% described the requirement).
sheet 1 and sheet 2 don't run in sequence. so

Sheet 1  A & C values need to look through all rows in sheet 2 coloums A & D.  There will only be one row of matching data.  Sheet 1 though has additional data witch is not in sheet 2.
0
 
barry houdiniCommented:
"Sheet 1  A & C values need to look through all rows in sheet 2 coloums A & D.  There will only be one row of matching data.  Sheet 1 though has additional data witch is not in sheet 2."
That's what my suggestion will do. formula in D2 will look through rows 2 to 100 in sheet2, if both values match in the same row then column Z value will be returned. If there are no matches then formula returns #N/A. You can make it return another value like "No match" if you change to this
=IF(SUMPRODUCT((A2=Sheet2!A$2:A$100)*(C2=Sheet2!D$2:D$100)),LOOKUP(2,1/((A2=Sheet2!A$2:A$100)*(C2=Sheet2!D$2:D$100)),Sheet2!Z$2:Z$100),"No match")
You can extend the ranges to cover as many rows as you need in sheet2
0
 
jstlawyersAuthor Commented:
Sorry missed that in the code.

Sorted great.

Thanks for this barry,

you get the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now