+ Reply to Thread
Results 1 to 6 of 6

Lookup multiple columns to return value

  1. 11-11-2021, 10:32 AM #1
    jhw1998
    jhw1998 is offline
    Registered User
    Join Date
    01-22-2021
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    23

    Lookup multiple columns to return value

    Good Afternoon all,

    I have a quick formula problem. As the title says I'm trying to return a value based on multiple criteria in sheet 1 column C (highlighted). I used the following formula orginally:

    =IF(ISNA(VLOOKUP(CONCATENATE(A2,"/",B2),Sheet2!A:D,4,0)),VLOOKUP(A2,Sheet2!B:D,3,0),VLOOKUP(CONCATENATE(A2,"/",B2),Sheet2!A:D,4,0))

    However, this only reads one list from sheet 2 as I am not advanced enough to know how to read more than one list lol. I know it seems I could just combine the lists in to one but my actual file has that much data in the lists I have to split them in to multiple columns.

    I am known to poorly explain what I mean in these threads so let me know if you need any more information.

    many thanks,

    JHW
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-11-2021, 10:44 AM #2
    Punnam
    Punnam is offline
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Lookup multiple columns to return value

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    HI ,
    TRY THIS
    Reply With QuoteRegister To Reply
  3. 11-11-2021, 10:46 AM #3
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Lookup multiple columns to return value

    In your type of scenario.

    Use a simple formula to return your answer for table One. If nothing found then return 0 or ""

    Use the same formula for tables 2 to 40

    Join the individual formulas wit either a + for numbers or & for strings

    =FormulaTable1() + FormulaTable2()

    =FormulaTable1() & FormulaTable2()
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.
    Reply With QuoteRegister To Reply
  4. 11-11-2021, 10:49 AM #4
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,814

    Re: Lookup multiple columns to return value

    You can use this formula in C2 of Sheet1:

    =IFERROR(VLOOKUP(A2&"/"&B2,Sheet2!$A:$D,4,0),IFERROR(VLOOKUP(A2,Sheet2!$B:$D,3,0),"SP"))

    Copy down as required.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  5. 11-11-2021, 10:49 AM #5
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,363

    Re: Lookup multiple columns to return value

    This ??

    =IFERROR(VLOOKUP(A2&"/"&B2,Sheet2!A:D,4,0),VLOOKUP(A2&"/"&B2,Sheet2!H:K,4,0))
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  6. 11-11-2021, 11:06 AM #6
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Lookup multiple columns to return value

    I converted everything to tables.

    Your first formula becomes:

    =IF(ISNA(MATCH(Table1[@[Acct '#]]&"/1",Table2[Acct No],0)),"",INDEX(Table2[#All],MATCH(Table1[@[Acct '#]]&"/1",Table2[Acct No],0)+1,4))

    Your second formula is

    =IF(ISNA(MATCH(Table1[@[Acct '#]]&"/1",Table3[Acct No],0)),"",INDEX(Table3[#All],MATCH(Table1[@[Acct '#]]&"/1",Table3[Acct No],0)+1,4))

    Basically identical to formula one with Table2 replaced with Table3

    So the combination is:

    =IF(ISNA(MATCH(Table1[@[Acct '#]]&"/1",Table2[Acct No],0)),"",INDEX(Table2[#All],MATCH(Table1[@[Acct '#]]&"/1",Table2[Acct No],0)+1,4)) &
    IF(ISNA(MATCH(Table1[@[Acct '#]]&"/1",Table3[Acct No],0)),"",INDEX(Table3[#All],MATCH(Table1[@[Acct '#]]&"/1",Table3[Acct No],0)+1,4))



    In the Second attachement I have entered the formula in the table.

    Paste CD321383 into Cell A22 on Sheet1 on the second attachment to see how useful this is.
    Attached Files Attached Files
    Last edited by mehmetcik; 11-11-2021 at 11:21 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Lookup formula to return multiple hits on multiple rows and values from multiple columns
    By Mattr13 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-12-2021, 07:21 PM
  2. [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
  3. Lookup multiple columns and return a value
    By lvan87 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-10-2014, 08:00 AM
  4. [SOLVED] Lookup multiple columns and return a value
    By coach.32 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-10-2014, 12:13 AM
  5. Want to lookup multiple matching criteria and return multiple data in columns
    By J Mercey in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-16-2013, 12:42 PM
  6. lookup multiple values in multiple columns to return a result
    By AYAHOO123 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-11-2013, 07:53 PM
  7. Please help, i need to lookup through multiple columns and return one value !!
    By abood53 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-17-2010, 03:26 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1