+ Reply to Thread
Results 1 to 5 of 5

VLOOKUP based on cell A or B

  1. #1
    Registered User
    Join Date
    03-14-2022
    Location
    Netherlands
    MS-Off Ver
    Professional Plus 2019
    Posts
    2

    Question VLOOKUP based on cell A or B

    Hello there,

    I am looking to create an Excel form that is used to register customers that come to my workshop. It's annoying for repeating customers to have to share their details every time they come in.
    This is why I have a VLOOKUP on all input cells (Address, email etc.) comparing the "name" cell with sheet the second sheet that stores the customer information.

    However, I would also like to be able to search this second sheet using the customers plate number. Is there a way to perform two VLOOKUPS in one cell and choose one of the two found cells based on their content?

    Ergo: Name cell is empty, plate number cell is populated > Address, email etc. get filled based on plate info
    And vice versa.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: VLOOKUP based on cell A or B

    do you have a sample sheet you can post ?

    See the yellow banner at the top of the screen

    As the Reg number Plate , is unique to a customer, would it be better just to always use that perhaps
    Then if they have multiple cars , it will still bring them up and be about that particular vehicle ?

    you can do an IFERROR
    and then use 2 vlookups
    so if the first does not return a value it will then look for the 2nd vlookup

    something like
    =IFERROR ( vlookup(version 1) , vlookup(version2) )
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    03-14-2022
    Location
    Netherlands
    MS-Off Ver
    Professional Plus 2019
    Posts
    2

    Re: VLOOKUP based on cell A or B

    Thanks for responding!
    Your suggestion to use an if(error) statement really helped. I spent a few hours on it and expanded a bit on the idea and it resulted in this formula:
    =IF(AND(ISFORMULA($C$9);ISBLANK($C$18));"";IF(ISTEXT($C$9);VLOOKUP($C$9;CustomerFile!$A$2:$H$9998;3;FALSE);XLOOKUP($C$18;CustomerFile!$H2:$H9998;CustomerFile!$C$2:$C$9998;"";0)))
    This formula is pasted in all the info cells (adress, email, phone, postal code etc.) but with incrementing column pointers.
    C9 is the customer name and C18 is the number plate.
    The first part of the formula is to check whether both user-entered cells are empty; which results in a blank cell if true.
    The second part first checks whether the user-entered field C9 has been typed in or has been left empty (indicating that the user is searching based on C18; not C9)
    If C9 has been typed in the search will look for the customer name in the database and pull data based on that. If C9 has been left untouched by the user the search will use C18 and pull data based on that.
    The XLOOKUP is because the information is to the left of the column mentioned in C18.

    Ergo:

    A B C D E F G H
    C9 C18

  4. #4
    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,900

    Re: VLOOKUP based on cell A or B

    If you still need help... please upload a sample sheet (yellow banner... top) as I'm not sure if you're good to go... or not.
    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

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

    Re: VLOOKUP based on cell A or B

    Please upload your sample file, we need it to solve.

    or else , you may use

    =IFError( 1st vlookup , 2nd vlookup )

    Regards.

+ 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. Cell formatting based on vlookup and multiple cell values
    By Big_Kev007 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-21-2021, 04:07 AM
  2. Pre-populate cell based on text in another cell! VLOOKUP not working :(
    By LeanneHMUA in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 10-17-2019, 10:19 AM
  3. Need to do vlookup based on whether cell contains @ or not
    By bwatkin79 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-03-2015, 11:49 PM
  4. Concatenate cell based on vlookup cell reference
    By tlc1980 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-14-2014, 04:06 PM
  5. [SOLVED] vlookup based on cell value
    By sudharshan86 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-19-2014, 04:17 AM
  6. Auto Populate cell based on Drop Down list in another cell (VLookup)
    By alialmoore in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-27-2013, 04:07 PM
  7. Replies: 6
    Last Post: 09-04-2012, 10:35 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