Lenovo / 9482b1u

Hello, My customers have two account numbers, one for each of two systems I use daily. I have a spreadsheet that uses VLOOKUP to automatically fill in the customer's name in the next column after entering the account number, referenced from another spreadsheet. Depending upon the circumstance I would like to be able to key in either account number to pull up the customer name associated with it.

My reference sheet contains three columns: 1st Account#, 2nd Account#, and Customer Name (3 columns):

24567894 10074857 John's Store

21854349 10083423 Jim's Store

21945433 10075234 Jack's Store

etc.Right now VLOOKUP works fine when keying any of the 1st Account#'s, but I would like to add another variable in which I have the option to key the 2nd Account# as well to retrieve the same customer name from the reference sheet (keying 21854349 -or- 10083423 would return "Jim's Store" as the result). I wouldn't use both account #'s at the same time, just one or the other. I can't get Excel to "see' the account #'s in the 2nd column and return the customer name as a result. Is there an OR function that I can add to the formula that will look at both columns?

Based on your example account numbers, how about an IF that looks at the first digit and does a VLOOKUP based on what it finds: With your data in A1:C3, enter an account in D1 and use this:

=IF(LEFT(D1,1)="2",VLOOKUP(D1,$A$1:$C$3,3,0),VLOOKUP(D1,$B$1:$C$3,2,0))

Hi, This 2-part VLOOKUP looks for the account number in column A using the MATCH function and if it finds a match it does a VLOOKUP on column A, returning the matching name from column C.

If there is no match for the account number in column A, it does a VLOOKUP on column B and returns the matching name from column C.=IF(ISNA(MATCH(E2,A2:A4,0)),VLOOKUP(E2,B2:C4,2,FALSE),VLOOKUP(E2,A2:C4,3,FALSE))In this example the account number was entered in cell E2.

Regards

...or better yet, to allow for data entry errors: =IF(ISNA(MATCH(E2,A2:A4,0)),IF(ISNA(MATCH(E2,B2:B4,0)),"Acct Not Found",VLOOKUP(E2,B2:C4,2,FALSE)),VLOOKUP(E2,A2:C4,3,FALSE))

I used elements of all three solutions and now have a working sheet that returns the customer name for either account number. This will save a countless number of keystrokes since I no longer will have to key the account name each time. The reference sheet is simple to update and sort, so I'll always be good to go! Thank you all so much!

Ask Your Question

Weekly Poll