Thread: how to link a1 to b1 to auto fill one or the other if entering info in only one.

Okay, I am pretty good with excel but this one is stumping me and I know it is an easy fix.

I have b3(account #) c3 (account name)
001100 Joe

I want to be able to just type either the account number OR name and the other to autofill the next cell because there are times I only know the name or the number and I dont want to look up the the information. I am currently at cell 543 so i have a lot of information. I know I could just do crtl f and find the information, but I just know there is a way. currently i have this formula on a differnt sheet =CONCATENATE('sheet 1'!D496," ",'sheet 1'!C496) and then listing it in ABC order so i can flip to that screen.

I was trying If then but it just wasnt working, i dont think i was doing the right cells. I will take the time to enter the information to get this going, but i dont have the time to be doing it every day. I am spending an hour a day on mail which should only take 20 minutes.

Thank you.

Do you want to the next cell to fill in the acct # if you put in the name and visa versa, or do you want the next cell to input other information, e.g. from a VLOOKUP? I would use an combination of IF and MATCH to determine if your inputted value is in column B or C and then proceed from there.

Code:

=IF(ISNUMBER(MATCH(J3,Sheet1!$B$3:$B$500,0)), VLOOKUP Based on Column B, IF(ISNUMBER(MATCH(J3,Sheet1!$C$3:$C$500,0)),VLOOKUP Based on Col C,"Does not exist))

Does that get you started in the right direction?

Okay, where are you entering the Acct # or Name? Is Sheet4 A2:A542 where you put your concatinated values? What do you want returned and where?
Example

In Sheet 4 Column B, the user will enter either the acct or Name. In Sheet4 Col C, Excel returns the corresponding value for that Acct (or name) from Sheet1 column G.
In Sheet4 Col D, Excel returns the corresponding value from Col G Sheet1, etc.

An example workbook would help (in xls format).