+ Reply to Thread
Results 1 to 11 of 11

Lookup within a Vlookup

  1. #1
    Registered User
    Join Date
    07-26-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    8

    Lookup within a Vlookup

    Dear All,

    I would appreciate if anyone could advise on the following.

    I have two seperate spreadsheets: Sheet 1 with data of 50 clients and Sheet 2 with data of 35000.

    I currently have a look up in place which looksup and matches client names (Column A on both sheets) from Sheet 1 to Sheet 2, and indicates "Possible Match" or "No Match".

    I wish to also create a lookup which can correlate matching dates of birth, but I need this to happen after the names have been correlated. The goal would be to have a key match column which only states possible matches if the name primarily, and date of birth secondarily appropriately match.

    Is there a way of doing this?

    The dates may be formatted slightly differently on each sheet, does this present a problem? e.g 10th Aug 2013 and 10-Aug-2013

    Many thanks for your time,

    Jose

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Lookup within a Vlookup

    Something like this?

    daffodils example for jozz90.xlsx

  3. #3
    Registered User
    Join Date
    07-26-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Lookup within a Vlookup

    This is exactly the concept I'm going for. Many thanks, but I have a couple of queries.

    1. Is this formula executed at a linear level? For example say Bob was born 02/09/1990, and his DOB on the second sheet did not correlate, but John also has the DOB 02/09/1990 non both sheets. Would this formular state a name and DOB match for Bob, by incorrectly taking John's DOB match from the second sheet?

    2. The two data sets are on two seperate documents. Could you advise on the equation used?

  4. #4
    Registered User
    Join Date
    07-26-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Lookup within a Vlookup

    I tried applying your formula to my spreadsheets as follows, but an error occurred.

    =IF(COUNTIF('[Document 1.xlsm]Sheet2'!$A$2:$A$36593,'[Document 2.xlsx]Document 2 Title'!$A$2)>0,IF(COUNT(IF('[Document 1.xlsm]Sheet2'!$A$2:$A$36593,'[Document 2.xlsx]Document 2 Title'!$A$2)IF('[Document 1.xlsm]Sheet2'!$I$2:$I$4759,'[Document 1.xlsm]Sheet2'!$I$2:$I$36593='[Document 2.xlsx]Document 2 Title'!$V$2,'[Document 1.xlsm]Sheet2'!$I$2:$I$36593))),"Name and DOB match","Name only match"),"No match")

    Could you assist as to what I am inputting incorrectly?

  5. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Lookup within a Vlookup

    On my sheet, select the Cell you want to copy.

    Use Ctrl+F, replace = with XXX

    Then copy it to your book, and Ctrl+F to reverse with subsituting XXX for =

    This will preserve the links.

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Lookup within a Vlookup

    Let me break down the formula logic you can apply however you need to:

    =IF(COUNTIF($I$3:$I$14,A2)>0,IF(COUNT(IF($I$3:$I$14=A2,IF($J$3:$J$14=B2,$J$3:$J$14))),"Name and DOB match","Name only match"),"No match")


    Here is how the formula applies to your ranges.

    =IF(COUNTIF(LISTOFNAMES,NAME1)>0,IF(COUNT(IF(LISTOFNAMES=NAME1,IF(LISTOFDOBS=NAME1DOB,LISTOFDOBS))),"Name and DOB match","Name only match"),"No match")

    Imagine LISTOFNAMES A1:A10 with names, and LISTOFDOBS B1:B10 with Dates of Birth, and then over at K1 you have a name, in L1 you have a date of birth. You want to find if K1 is anywhere in A1:A10, and also if L1 is in B1:B10.

    Using those reference, the formula would look like this:
    =IF(COUNTIF(A1:A10,K1)>0,IF(COUNT(IF(A1:A10=K1,IF(B1:B10=L1,B1:B10))),"Name and DOB match","Name only match"),"No match")


    The first part of the formula counts to number of matches. If it doesn't find one, "No Match"

    If it does find one, it then checks dates of birth only on the rows it found matches for names. If it finds there is one, "Name and DOB match" and if it doesn't "Match Only" since it already passed the first test meaning there is at least one name match.

    Remember to enter the formula as an array using Ctrl+Shift+Enter when exiting the cell.

  7. #7
    Registered User
    Join Date
    07-26-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Lookup within a Vlookup

    I am eternally grateful. Formula up and running, revolutionising my data collating.

    Many thanks!

  8. #8
    Registered User
    Join Date
    07-26-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Lookup within a Vlookup

    Spoke to soon!

    This is my formula: =IF(COUNTIF('List Full'!A3:A36594,'Check List'!A3)>0,IF(COUNT(IF('List Full'!A3:A36594='Check List'!A3,IF('List Full'!I3:I36594='Check List'!V3,'List Full'!I3:I2I36593))),"Name and DOB match","Name only match"),"No match")

    The formula is processing fine, but the the match is stating "Name and DOB match" where there is no DOB match.

    To also note I am regularly migrating data in sets of 50 from Checklist and correlating it with a static database 'Full List'. When the data is finished with they are deleted. Do blank cells have an effect on the formula?

    Kind regards,

  9. #9
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Lookup within a Vlookup

    Shouldn't be an issue, as long as you are deleting contents of cells and not the actual rows.

    If it prompts to you move data left, or up, you're deleting cells and not just what's in them.


    Feel free to post another sample. I'll troubleshoot anything I can. I'm here another 6 hours or so.

  10. #10
    Registered User
    Join Date
    07-26-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Lookup within a Vlookup

    Hi again,

    The fomula I'm using is this

    =IF(COUNTIF('Dormant Account List Full'!A:A,'BOE Check List'!A6)>0,IF(COUNT(IF('Dormant Account List Full'!A:A='BOE Check List'!A6,IF('Dormant Account List Full'!I:I='BOE Check List'!V6,'Dormant Account List Full'!I:I))),"Name and DOB match","Name only match"),"No match")

    When I double click a cell which says "Name only match" then press enter, it will turn into "Name and DOB match"
    even when dates are not correlated.

    Why is this?

    Many thanks

  11. #11
    Registered User
    Join Date
    07-26-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Lookup within a Vlookup

    Also to note, upon second migration of data, the formula only works to correlate names; but labels them "Name and DOB match" even when DOB's are not correlated

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel 2007 : Lookup/Vlookup using two Lookup values
    By Rick K in forum Excel General
    Replies: 4
    Last Post: 09-16-2010, 02:16 PM
  2. Lookup within a vlookup
    By SheilaV in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-05-2010, 03:01 PM
  3. lookup vs vlookup
    By keith6292 in forum Excel General
    Replies: 3
    Last Post: 12-18-2009, 02:59 PM
  4. vlookup-what the lookup value is?
    By enyaw in forum Excel General
    Replies: 1
    Last Post: 05-08-2006, 05:35 AM
  5. LOOKUP or VLOOKUP
    By Bennie in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-24-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1