+ Reply to Thread
Results 1 to 10 of 10

Creating a Vlookup formule with two search criteria

  1. #1
    Registered User
    Join Date
    04-08-2021
    Location
    Belgium Brussels
    MS-Off Ver
    2016
    Posts
    5

    Question Creating a Vlookup formule with two search criteria

    Hello everyone, I'm glad I found this website ! It's been really helpful for my work.

    I am trying to create a "vlookup" formula with two criteria but I don't know how to write it. I have two separate tables: The first one contains three columns: the name of the country, monthly consumer price index (CPI) since 2010, date of the corresponding CPI (in the format mmm-yy). The second one also contains three columns: Country name, country iso (in two letters), date of last indexation for that country (in mmm-yy format)

    So in the first table, there are many times the same country name because there are many different dates (the CPI statement is available for all months)

    My goal is to create a column in the second table, displaying the CPI corresponding to the country name AND the date of the last indexing (in the mmm-yy format). I guess I need to create a Vlookup formula that looks for a match for the country name and the date in the first table ?

    Thank you very much in advance.

    Thomas.
    Attached Files Attached Files
    Last edited by MsterShifu; 04-08-2021 at 09:10 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,412

    Re: Creating a Vlookup formule with two search criteria

    Attach a sample workbook (not image).so that we do not have to manually key in your data to do any testing.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,848

    Re: Creating a Vlookup formule with two search criteria

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    Glenn



  4. #4
    Registered User
    Join Date
    04-08-2021
    Location
    Belgium Brussels
    MS-Off Ver
    2016
    Posts
    5

    Re: Creating a Vlookup formule with two search criteria

    I'm sorry, I just modified my post to add a small example.

    On the second sheet, I put on a green background the information I would like to have through a formula.

    Thanks,

    Thomas

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,412

    Re: Creating a Vlookup formule with two search criteria

    In D2

    =INDEX(Feuil1!$C$1:$C$100,AGGREGATE(15,6,ROW(Feuil1!$D$1:$D$100)/((Feuil1!$A$1:$A$100=$A2)*((Feuil1!$B$1:$B$100=$B2))),ROW(Feuil1!$D$1:$D$100)))

  6. #6
    Registered User
    Join Date
    04-08-2021
    Location
    Belgium Brussels
    MS-Off Ver
    2016
    Posts
    5

    Re: Creating a Vlookup formule with two search criteria

    Thank you. When I try your formula, I got a "#SPILL!" error on the cell. Any idea why ?

    I also had to replace all the "," with ";" to make it work, but I think it's because of the 2016 suite.

    Thanks again,

    Thomas
    Attached Images Attached Images
    Last edited by MsterShifu; 04-08-2021 at 02:47 PM.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,412

    Re: Creating a Vlookup formule with two search criteria

    in have Excel 2010 so i cannot help with the SPILL error but am surprised the formula does not work with a later version of Excel.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-08-2021
    Location
    Belgium Brussels
    MS-Off Ver
    2016
    Posts
    5

    Re: Creating a Vlookup formule with two search criteria

    Thanks for sharing it. The formula is now functional, office 2016 just require an "at" before the INDEX formula.

    Have a great day,

    Thomas
    Attached Images Attached Images

  9. #9
    Registered User
    Join Date
    04-08-2021
    Location
    Belgium Brussels
    MS-Off Ver
    2016
    Posts
    5

    Re: Creating a Vlookup formule with two search criteria

    I'm sorry to be back, but I just have one problem. Is it possible to search only for a matching month & year and not bothering about the day in the date? Sometimes I get an error because it's not the exact same date, but it's very close.

    Could it be possible to search for the closest match ?


    Thomas

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,505

    Re: Creating a Vlookup formule with two search criteria

    If AR and 3-Feb does not exist in Feuil1, it returns smaller closest date 2-Feb:

    =LOOKUP(2,1/(Feuil1!$A$2:$A$87=A2)/(Feuil1!$B$2:$B$87<=B2),Feuil1!$C$2:$C$87)

+ 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. [SOLVED] Need help with a VLOOKUP formule with two criteria...
    By HosteDenis in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-12-2020, 12:36 PM
  2. Replies: 7
    Last Post: 11-23-2015, 02:04 AM
  3. [SOLVED] Vlookup vba code formule not working
    By brainzlp in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-16-2015, 02:41 PM
  4. Creating search criteria and chart in macro VBA
    By hanif in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-03-2014, 11:50 AM
  5. Replies: 4
    Last Post: 05-20-2014, 10:03 AM
  6. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  7. Creating different tables using Formule
    By tapanshah in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-14-2012, 01:50 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