+ Reply to Thread
Results 1 to 6 of 6

Combining VLookup and HLookup

  1. #1
    Registered User
    Join Date
    09-01-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    3

    Combining VLookup and HLookup

    Hello,

    I need help to combine a Vlookup and Hlookup and I'm not sure how to do it.

    I need to find the value in column A (airport code) on the Results sheet in column B(airport code) on the Zip Code sheet. Then I need to determine if the zipcode in column c on the results sheet is a local zip code to the airport code from the zip code sheet. Any N/A errors are assumed to be Not Local and are omitted from my counts.

    I set up an Hlookup in column D on the Results sheet using only the rows that matched the airport code, but I have a list of all the airport codes in the US and a list of local zipcodes for all the airports. Some airports have over 1,000 possible matches that could be local. So it would be ideal to have a formula that combines the H&Vlookup.

    I attached sample data to show what I have acheived thus far, but every tutorial I have tried does not work when I try to nest the Hlookup in my Vlookup. Please help!

    Naomi
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Combining VLookup and HLookup

    Inconsistency: From your airport zip table every airport code contains two lines, the second line is always blank in column A.
    So why does A5 contain "Local"? Why isn't it blank like the rest?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    09-01-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    3

    Re: Combining VLookup and HLookup

    Special-K,

    My mistake in column A. It should be "local" under each airport code in column A as well because the Hlookup currently returns a "0" value because it recognizes the Airport Zip Code first. I should be able to switch columns A & B if it would make writing the formula easier because the Airport Zip Code is technically has the closest zip radius to the airport.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combining VLookup and HLookup

    Try pasting the following into cell E2 on the Results sheet and then copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    09-01-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    3

    Re: Combining VLookup and HLookup

    JeteMc,

    Thank you, this worked in my sample data. Can you explain the different parts of the formula so I can understand and replicate?

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Combining VLookup and HLookup

    SUMPRODUCT is used as it handles arrays of values, usually without activating by simultaneously pressing the Ctrl, Shift and Enter keys (CSE), so the formula could read:
    IF the zip code in cell C2 is equal to one of the INDEXed zip codes in cells C2:K11, on the row in which the value in cell A2 MATCHes one of the zip codes in B2:B11, then display 'Local' else display 'Not Local'.
    Note that the zero in the ,0)) portion of the formula causes the formula to INDEX a row of values as opposed to one value.
    Note that if SUMPRODUCT yields any number other than zero the IF treats the number as Boolean TRUE, IF treats zero as Boolean FALSE.
    One of the better ways to see what the formula is doing is to utilize the Evaluate Formula feature on the Formulas tab.

    BTW, You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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] combining vlookup and hlookup to extract data on another sheet
    By DaveLearner in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-22-2019, 04:54 PM
  2. Combining VLookup & HLookup
    By CaseyNoyes in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-14-2018, 02:10 PM
  3. [SOLVED] Combining vlookup and hlookup (urgent) :'(
    By Klovers in forum Excel General
    Replies: 6
    Last Post: 05-15-2015, 08:55 AM
  4. Combining VLOOKUP and HLOOKUP ?
    By fannta in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2015, 12:16 PM
  5. Combining two worksheets using Vlookup and Hlookup.
    By lovinguy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-07-2014, 09:52 AM
  6. Combining Vlookup with Hlookup
    By Mugendi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-05-2014, 08:32 AM
  7. Vlookup & Hlookup combining separate workboo
    By Amak30 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-02-2010, 10:28 AM

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