+ Reply to Thread
Results 1 to 33 of 33

Lookup multiple columns

  1. #1
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Lookup multiple columns

    Basically I want to search up to up to 4 columns with 4 other colums and see if they match?

    For instance, I want to search by an invoice number and see if the line matches three different price points from one sheet to three separate excel sheets and then highlight all the the rows that match on both files.

    I'm not sure what formula to use like index and match and how to set it up.

    Example


    Sheet 0
    Column A - B - C - D
    Invoice - Price 1 - Price 2 - Price 3

    3 Excel sheets
    Column A - B
    Sheet 1) Invoice - Price 1
    Sheet 2) Invoice - Price 2
    Sheet 3) Invoice - Price 3

    How do I match these to see which prices from Sheet 0 are in sheet 1 and so on? And then highlight with a color automatically? Invoices are all the same across all the sheets.
    Last edited by lickhittuh; 10-22-2019 at 06:49 PM.

  2. #2
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup multiple columns

    Suppose your data structure is

    Please Login or Register  to view this content.
    Your formula for each cell can be
    B2
    Please Login or Register  to view this content.
    C2
    Please Login or Register  to view this content.
    D2
    Please Login or Register  to view this content.
    By the way, above formula are refer 'ALL Column' that many experienced EXCEL's user not suggest to use,
    so if you can definite (or limited or known the last row of each sheet please change them)


    Regards.

    Ps. I don't use SUMIF to avoid 'duplicate' Invoice in each sheet (if any).
    Last edited by menem; 10-22-2019 at 10:22 PM. Reason: Change layout and add note.

  3. #3
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    Thanks, gonna have to test this.

    Also once they’re found or not found, how do I automatically highlight or distinguish the found ones on all of the sheets?
    Last edited by lickhittuh; 10-22-2019 at 10:48 PM.

  4. #4
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup multiple columns

    For hi-light cells in Sheet1 to Sheet3 in cells that was specify in Sheet0
    you may use condition format for each cells in A_Column of Sheet1 to 3.

    Formula in condition format:

    Cell_A1
    =COUNTIF(Sheet0!$A:$A,A1)>0

    Bye the way, if you search more than one invoice in Sheet0 , all that was search (and found) will also hi-lighted.


    Regards,

  5. #5
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    So I put the formulas all in sheet 0 next to the original columns?

    Do you think you could create an excel file with 4 sheets and random numbers as an example with the formulas in them? I would really appreciate it.

  6. #6
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup multiple columns

    Please see attached file.

    Note : Sheet1-3 are full randomized so it might be not found same invoice in all sheets.

    Regards.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    So I'm still not getting it because the cells in Sheet 0 have values so I can't just put a formula in them. Which is why I asked earlier where should I put the formula. I guess I didn't explain my issue well enough initially, sorry.

    I tried to attach an Excel sheet but the forum isn't working. Here's a link to it, replace the [dot] with an actual dot for the URL. It's what I want my end result to look like. Can you input the formulas in that so it ends up exactly how I have it? Along with the color coding I provided, doesn't have to be that color, it can be highlights, just need something to visually distinguish them. Invoices 1-5 match and are color coded while Invoice 6 doesn't match.

    Also do you have any ideas on what to do if Sheet 0 invoice numbers have hyphens, periods, and spaces in the cells because sometimes Sheets 1-3 don't have the hyphens. And when dealing with thousands of rows it seems like I might still need to go back and check the rows that don't completely match. Wondering if it's possible or not to fully automate this because of that problem. Is there a code to void those things when searching?
    Last edited by lickhittuh; 10-24-2019 at 10:57 AM.

  8. #8
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup multiple columns

    As you mention that you can not upload file please follow by this ...
    Please Login or Register  to view this content.
    Do not use others method to upload files.

    You doubt where to place formulas , your invoice number layout, I'll try my best to explain (due to my low level of English T_T ).

    1. In Sheet0 , Formula is only in B_Column, C_Column and D_Column
    A_Column contains only INVOICE NUMBER that without hyphen (-)
    In a sample file I use formula for randomize invoice number , sorry to make you confuse.
    Formula in B,C,D_Colume have been change to support hypen in price sheets.
    Please Login or Register  to view this content.
    All of them need Ctrl-Shift-Enter for Array Formula.
    And again please remember to change 'all column' reference to definite range of your data rows for better performance.
    (ie $B:$B => $B$1:$B$2000, $A:$A => $A$1:$A$2000)


    2. In price sheets ( Sheet1, Sheet2, Sheet3 )
    All of them are values (but in a sample file I use formula for randomize too)
    And I've add a title row in these sheet, use a 'number' as price instead of showing text as before.

    3. I've change condition formatting in price sheets (Sheet1,Sheet2,Sheet3) for support that
    Some invoice number in these sheets may contains hyphen (-).
    Formula is (at cell A2 in Sheet1,Sheet2 and Sheet3)
    Please Login or Register  to view this content.
    Don't forget that invoice number in Sheet0 must not contain hyphen (-), while invoice number in price sheets may contains hyphen.

    Regards.


    Please note : hy-light in price sheets is only tell that they was listed in Sheet0.
    Attached Files Attached Files
    Last edited by menem; 10-23-2019 at 10:20 PM.

  9. #9
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    Okay I deleted that link, I think you've mostly already answered my question but I think I figured out how to post an Excel file now, it's attached so you can see it.

    There's still some miscommunication. Maybe just from my end not understanding but Sheet 0 Columns B-D already have values there can't be just formulas in those cells. And how do I make it so the value in Sheet 0 stays in tact instead of saying "Not Found" because I need to be able to see the number that wasn't found. I'd just like to color code it like in my example Excel file where I color coded Invoices 1-5 which were found ones and didn't color the one that wasn't found which was Invoice 6.

    Also sometimes Sheet 0 contains hyphens and the sheets don't. It can happen either way. If this is a problem can you just remove the hyphen coding and I'll deal with the hyphens manually? Cause it's not just hyphens, sometimes it's periods an other stuff too. I've list the examples in the attached Excel file where there are hyphens and periods in Sheet 0 and Sheet 1 but they still match with the color coding.
    Attached Files Attached Files
    Last edited by lickhittuh; 10-24-2019 at 11:38 AM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,391

    Re: Lookup multiple columns

    No, you have not attached a workbook. Try again.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  11. #11
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    No it worked, I just deleted it to make a change. It works now.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,391

    Re: Lookup multiple columns

    OK - thanks.

  13. #13
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    That's odd that when you try to make an attachment next to the Emoji's at the top it won't let you do it and says you need more forum posts but it works at manage attachments. Maybe the restriction needs to be lifted cause that's where my confusion happened, just a suggestion.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,391

    Re: Lookup multiple columns

    There is no restriction on making attachments, but the paperclip icon simply doesn't work (the tech team have failed to fix it despite repeated requests to do so - they don't seem to care).

    I am far from clear about what you want to do in this workbook. It looks to me as if Sheet 0 is merely an amalgam of the other three. Perhaps you could explain in different words what you are trying to achieve?

  15. #15
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    It is. Sheet 0 has almost all of the same information that Sheets 1-3 have, with some inconsistencies. I need to find the ones that match and don't match. They're just separated because they're listed until different payments. Like Sheet 1 is a payment for one entity, Sheet 2 is another, and Sheet 3 is also another. I want to color code the ones that match and the ones that don't match for verification purposes. There are usually thousands of lines so it's time consuming having to do them one by one manually.
    Last edited by lickhittuh; 10-24-2019 at 11:48 AM.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,391

    Re: Lookup multiple columns

    Why would there be inconsistencies? There's no need to copy the data manually - this can be automated. I am not really getting the purpose here - your sample data is probably too simplistic to show why this is necessary.

  17. #17
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    Inconsistencies can be caused by user input and when you're trying to reconcile the totals for one entity even a few pennies can throw off the total and you can't balance it.

    I'm not completely sure on the broad overview of the problem but I do know that later, the ones that match needed to be added up from Sheet 1, for example, to get a total sum for matched payments that went through or something.

    I don't think my sample data is too simplistic, that's basically what I'm looking at but thousands of rows.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,391

    Re: Lookup multiple columns

    OK - well, it's not sufficient for me to be able to help, I am afraid. Hopefully someone else will manage to work it out.

  19. #19
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    menem has already done it pretty much, just need some tweaking/clarification from him on cell values and hyphens/periods

  20. #20
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup multiple columns

    Oh, seem I've misunderstood. >_<

    May I re-confirm your requirement again?

    1. All sheet have the same invoice
    2. Invoice in sheet0 may have different format
    ( - . and space) while others just plain.
    3. Just want to hi-light on price (match/don't match)

    Regards.

  21. #21
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup multiple columns

    While waiting , I've create a new file as I think it's fit your request (by my side ^_^ )

    This file use many of condition formating as you will see , but I still cannot verify from Sheet1-3 to Sheet0
    due to I don't know how to manage Array formula in Condition format formula, so, I've to create a helper column in Sheet0
    (which remove - . and space away).

    Please verify the file.


    Regards.

    Ps: Condition format formula is different in each column , don't copy across them.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    Yes, that's exactly what I want. Thank you.

    How do I apply what you've done in your file to my file so it's automatic and I can use it for any set of data? The formulas I see in your file are in the helper column and under the True cell in the other sheets. I just copy both of those to my file?
    Last edited by lickhittuh; 10-25-2019 at 07:48 AM.

  23. #23
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup multiple columns

    No, you need to copy helper_column
    And condition_format of each column in each sheet
    (They are all different)


    True cells just for help me easier to write condition format formula..

    Regards.

    Beware , don't change columns..
    And must copy cell format enough for your data.

  24. #24
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    Okay I copied them but nothing happened with the conditional formatting. What do I change?

    Here's what I'm looking at.

    Edit - Check it now, reuploaded
    Attached Files Attached Files
    Last edited by lickhittuh; 10-25-2019 at 09:13 AM.

  25. #25
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup multiple columns

    I was test them. Bad news, you cannot copy formats and formulas from my file.
    Good news, you can copy your data into my file instead.

    Seem , when you copy, all formula still try to link back to original (my file). So it won't work as you want in the first time.

    Regards.


    Ps. the other way , you can copy helper column formula.
    then just try to copy formula in each condition format one-by-one into your file.

    Sheet0 A = 3 formula
    Sheet0 B,C,D = 2 formula ( total = 6 )

    Sheet1-3 A = 2 formula (total = 6)
    Sheet1-3 B = 2 formula (total = 6)

    total 21 condition format formula to copy.
    Last edited by menem; 10-25-2019 at 09:28 AM.

  26. #26
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    That works when I copy my data into your file. Great, thanks a lot man. I really appreciate it!

    The other way is confusing right now but the first way works.

  27. #27
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    Actually can we add another column called Policy Number next to the Invoice number with the same criteria with the hyphens/periods.

    Can you also do that and upload the Excel file and I should be good to go.
    Last edited by lickhittuh; 10-25-2019 at 09:47 AM.

  28. #28
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup multiple columns

    Maybe but now I'm on the road, you might to wait ,
    You can tell info about policy...now.
    And others can help you before I came back.

    ^_^

    Regards.

  29. #29
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    Okay, there's no rush, anytime will be fine.

    The only info is about policy is that it's just another information column I want to add on all the sheets. Like Policy Number in column A, Invoice Number on column B, and the Prices in the rest of the columns and they can all match up or partially match. Usually the Policy number and Invoice number always match up but sometimes there might be a mistake.
    Last edited by lickhittuh; 10-25-2019 at 10:11 AM.

  30. #30
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    Hey I no longer need the extra policy column. So it's done, you don't have to add anything.

    Thanks again for all the help!

  31. #31
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,391

    Re: Lookup multiple columns

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  32. #32
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup multiple columns

    Oh. I just finish a new ver (include policy).
    This file change method for condition format checking by check on cell value (result from formula)
    instead of formula that check across sheets (it will more easier to debug change etc.)

    All formula are based on these functions.
    MATCH
    INDIERCT
    IF
    IFERROR
    LOOKUP



    Regards.
    Attached Files Attached Files

  33. #33
    Registered User
    Join Date
    10-22-2019
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup multiple columns

    I think for my purposes the first one worked really well, but thanks for the extra functions. It's appreciated!

+ 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] Lookup multiple criteria in multiple columns in multiple rows; return true if exists
    By ufdlim in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-07-2019, 02:03 PM
  2. Replies: 3
    Last Post: 04-08-2014, 03:11 AM
  3. [SOLVED] VLookup - Single value lookup returning multiple records into multiple columns
    By kllovin in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-07-2014, 05:14 AM
  4. Replies: 0
    Last Post: 10-16-2013, 12:42 PM
  5. [SOLVED] Multiple lookup adding columns, then multiplying, then lookup, add, and subtract
    By mamig in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-08-2013, 12:55 AM
  6. Lookup Data in One Column, Find Multiple Entries in Next, Copy to Multiple Columns
    By nzxt1234 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2010, 01:17 AM
  7. Lookup adjacent column to multiple lookup columns.
    By JAMES4228 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-13-2009, 03:19 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