+ Reply to Thread
Results 1 to 13 of 13

Combining VLookup & HLookup

  1. #1
    Registered User
    Join Date
    12-12-2018
    Location
    Maine
    MS-Off Ver
    Excel
    Posts
    5

    Unhappy Combining VLookup & HLookup

    I am trying to combine the VLookup and HLookup functions.... I have a workbook with 3 tabs.... Tab1 is a table with 3 columns and Tab3 is a table in which I'm trying to import information from Tab1. Basically I'm trying to do this:

    If information in cells A1+B1 (combined) on Tab1 equal the combination info in cells A3 and C2 on Tab3, then cell C3 is equal to what's in cell C2 on Tab1.


    I tried to do an attachment but of coarse it wont' let me.... I'm pretty sure I'm on the right track with combining the lookup functions but just cannot seem to complete it.


    Please help!!!!
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Combining VLookup & HLookup

    is this what you are looking for in cell C3 on the table tab?
    =INDEX(Data!$C$2:$C$2744,MATCH($A3,Data!$A$2:$A$2744,0),MATCH(C$2,Data!$B$2:$B$2744,0))
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    12-12-2018
    Location
    Maine
    MS-Off Ver
    Excel
    Posts
    5

    Re: Combining VLookup & HLookup

    Sambo Kid.... EXACTLY!!! Is there a way to populate to the right or do I need to do one column at a time? Make sense?

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Combining VLookup & HLookup

    the formula is designed to be dragged to the right or down.

  5. #5
    Registered User
    Join Date
    12-12-2018
    Location
    Maine
    MS-Off Ver
    Excel
    Posts
    5

    Re: Combining VLookup & HLookup

    Sambo Kid.... that is what I thought, but when I do I get the "#Ref" message, like the cell doesn't like something in the formula.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Combining VLookup & HLookup

    That formula won't work with your layout. You'd need something like:

    =IFERROR(LOOKUP(2,1/($A3=Data!$A$2:$A$2744)/(C$2=Data!$B$2:$B$2744),Data!$C$2:$C$2744),"")
    Rory

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Combining VLookup & HLookup

    try this instead in cell C3 of the table tab.
    =SUMPRODUCT((Data!$A$2:$A$2744=$A3)*(Data!$B$2:$B$2744=C$2),Data!$C$2:$C$2744)
    drag it down and right and it should return the values.

  8. #8
    Registered User
    Join Date
    12-12-2018
    Location
    Maine
    MS-Off Ver
    Excel
    Posts
    5

    Re: Combining VLookup & HLookup

    Sambo Kid and rorya….. any idea why both the IFERROR & SUMPRODUCT functions are returning duplicate values in the table tab?? See attached.
    Both suggestions work great other than this problem on both.

    Capture1.PNG

  9. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Combining VLookup & HLookup

    You're looking up the same values, so you'll get the same results back.

  10. #10
    Registered User
    Join Date
    12-12-2018
    Location
    Maine
    MS-Off Ver
    Excel
    Posts
    5

    Re: Combining VLookup & HLookup

    I guess I'm not explaining something correctly or understanding what you are saying.... what I am trying to do is get the information in Tab1 (Data) to show on Tab3 (Table) in a manner so the 'cost' cell on Tab1 will be applied to the correct Equipment ID and description code in the
    table for tracking purposes to know total cost on each equipment ID per description code.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Combining VLookup & HLookup

    You are misunderstanding what's being said, I think... Look at the yellow colured cells in column A of Table. They are duplicates. So, of course they will return the same values from data...

    My formula i the attched sheet is slightly different to thise above... In C3, dragged across and down:

    =IFERROR(INDEX(Data!$C$2:$C$2743,MATCH(1,INDEX(($A3=Data!$A$2:$A$2743)*(C$2=Data!$B$2:$B$2743),0),0)),"")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  12. #12
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Combining VLookup & HLookup

    Ok, looking at your data in the table tab, you have 15121 twice in col A. Looking at the data tab you have 15121 24 times. 15121 and code 11-0-0-1 is in the data table once for 7017.70 and it comes back in your table tab twice in cells D5 and D6. And it does that because the same two items are there. What result do you expect when the formulas are matching the two requirements, cells A5 and A6 are both 15121 and cell D2 is 11-0-0-1.

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Combining VLookup & HLookup

    I echo what everyone else has said.

    See the attached. I have selected A3:B186 in 'Table' tab and applied Remove Duplicates. Then deleted the unnecessary formulas. This eliminates 9,828 calculations.

    Also another formula approach. In C3 filled down and across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Does this do what you want?
    Attached Files Attached Files
    Dave

+ 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 (urgent) :'(
    By Klovers in forum Excel General
    Replies: 6
    Last Post: 05-15-2015, 08:55 AM
  2. Combining VLOOKUP and HLOOKUP ?
    By fannta in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2015, 12:16 PM
  3. 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
  4. Combining Vlookup with Hlookup
    By Mugendi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-05-2014, 08:32 AM
  5. Vlookup & Hlookup combining separate workboo
    By Amak30 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-02-2010, 10:28 AM
  6. Replies: 4
    Last Post: 10-28-2009, 04:28 PM
  7. Combining index and hlookup?
    By thisiscrazy in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-21-2009, 05:33 PM

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