+ Reply to Thread
Results 1 to 5 of 5

Lookup value in multiple columns

  1. #1
    Registered User
    Join Date
    04-28-2012
    Location
    Amsterdam
    MS-Off Ver
    Excel 2003
    Posts
    4

    Lookup value in multiple columns

    Hi there,

    On sheet 2 I have a table with data spread over different columns
    On sheet 1 I have a table were I would lik to show certain data per "UniekNummer"

    I attached a Test-document

    Below more explanation about the Test-document and my wish
    I hop you understand my and can help me with this


    So I want to lookup UniekNummer WA-AMS1902 000012 on sheet 2

    Excel must search on sheet 2 I want to on the row of that UniekNummer if in one of the columns is "BinnenBeoogdeReactieTijd".
    If yes, then show "BinnenBeoogdeReactieTijd" on sheet 1 next to the UniekNummer
    If no, then Excel mus check on sheet 2 if in one of the columns is "BuitenBeoogdeReactieTijd"

    If yes, then show "BuitenBeoogdeReactieTijd" on sheet 1 next to the UniekNummer
    If no, then on sheet 1 show "ReactieTijdNogOnbekend" next to the UniekNummer
    Attached Files Attached Files
    Last edited by Prymic; 03-13-2019 at 01:09 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    16,590

    Re: Lookup value in multiple columns

    No file attached

  3. #3
    Registered User
    Join Date
    04-28-2012
    Location
    Amsterdam
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Lookup value in multiple columns

    Sorry, just now I attached the file

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,869

    Re: Lookup value in multiple columns

    This proposal employs two helper columns which may be moved and/or hidden for aesthetic purposes.
    C1 is populated with BinnenBeoogdeReactieTijd
    D1 is populated with BuitenBeoogdeReactieTijd
    C2:D2 and down are populated using: =SUMPRODUCT((Blad2!$A$2:$A$600=$A2)*(Blad2!$B$2:$D$600=C$1))
    B2 and down are populated using: =IF(C2>0,C$1,IF(D2>0,D$1,"ReactieTijdNogOnbekend"))
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Contributor
    Join Date
    08-14-2018
    Location
    Hanoi, Vietnam
    MS-Off Ver
    2016
    Posts
    118

    Re: Lookup value in multiple columns

    To avoid hardcoding strings, insert a new table like this
    Capture.PNG
    Then put this formula in B2
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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