Vlookup one of two values for same result

Lenovo / 9482b1u
August 27, 2010 at 14:14:55
Specs: Microsoft Windows XP Professional, 2.194 GHz / 1014 MB

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

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?

See More: Vlookup one of two values for same result

Report •

August 27, 2010 at 14:29:46
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:


Report •

August 28, 2010 at 05:06:20

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.


In this example the account number was entered in cell E2.


Report •

August 28, 2010 at 06:13:24
...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))

Report •

Related Solutions

August 30, 2010 at 11:10:29
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!

Report •

Ask Question