# 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
 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 Store21854349 10083423 Jim's Store21945433 10075234 Jack's Storeetc.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

#1
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: =IF(LEFT(D1,1)="2",VLOOKUP(D1,\$A\$1:\$C\$3,3,0),VLOOKUP(D1,\$B\$1:\$C\$3,2,0))

Report •

#2
August 28, 2010 at 05:06:20
 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

Report •

#3
August 28, 2010 at 06:13:24