Vlookup within an If statement Excel

Posted on 2009-02-12
Medium Priority
Last Modified: 2012-06-21
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.


Question by:jstlawyers
  • 2
  • 2
  • 2
LVL 17

Expert Comment

ID: 23622735
Try this formula in Sheet 1 in cell D1 and copy down:

LVL 17

Expert Comment

ID: 23622746
Here is an example I created in a file:
LVL 50

Accepted Solution

barry houdini earned 1400 total points
ID: 23622882
In sheet 1 D2 try this formula copied down
see attached
extend ranges as necessary
reagrds, barry

 The Evil-ution of Network Security Threats

What are the hacks that forever changed the security industry? To answer that question, we created an exciting new eBook that takes you on a trip through hacking history. It explores the top hacks from the 80s to 2010s, why they mattered, and how the security industry responded.


Author Comment

ID: 23623306
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.
LVL 50

Expert Comment

by:barry houdini
ID: 23623648
"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

Author Comment

ID: 23623759
Sorry missed that in the code.

Sorted great.

Thanks for this barry,

you get the points.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question