Data Validation to Auto-Populate Adjacent Cell

[abrewer1]

Good afternoon!

I'm very new to using formulas and data validation in Excel, so I apologize if this question has been answered already and I didn't catch it. 

I'd like to see how to use Data Validation, a formula, or a combination of the two to be able to enter a specific number in one cell so that the information tells the adjacent cell to then auto-populate with the appropriate corresponding information.

I have a sheet with 7,864 rows of accounts & 2 columns (screenshot 1). As you'll see in screenshot 2, I need the data from the sheet (screenshot 1) to validate the data in columns J, K, L, & M (screenshot 2)

• Columns J & L: this is where I will type an Account Number.
• Columns K & M: this is where the Account Name that corresponds to the Account Number needs to go.

My goal is to be able to type in an Account Number in Columns J & L and for that to tell the next  column of the same row (Columns K & M) auto-populate with the correct Account Name. All of this would be done using the the 7864 rows of data in screenshot 1.

Any (very dumbed down) assistance you can offer is GREATLY appreciated!!

[#2 · Accepted solution]

Hi @abrewer1 

What I understood from your post is that you need corresponding data upon inputting Account Number. You may achieve this by using Index & Match function.

Sample file is attached, please let me know if this works for you or otherwise.

Tauqeer

[#3]

@tauqeeracma 

I really appreciate you following up with me so quickly on this. I've tried your suggestion, but instead of having the content that you have in columns A & B, I've moved it to another sheet within the same workbook. I'm not sure if that is the reason, but I'm getting an #N/A error. The formula I'm using is =INDEX(Sheet2!$B$2:$B$7864,MATCH(J2,Sheet2!$A$2:$A$7864,0))

I've attached a new screenshot so you can see the formula and error. 

Thanks so much!

[#4]

I figured out the issue! It was because the data in Sheet2 was inserted as "From Text/CSV". I went back and inserted the data in Sheet2 as just a copy/paste and it worked!! You just made my job so much simpler! Thank you!!

[#5]

@abrewer1 You are welcome

[#6]

Hi @tauqeeracma!

Thank you so much for your time here helping us!

I followed the sample that you uploaded in this thread for @abrewer1 (I'm using 365), and it worked! Thank you!!

Question though...

I used this formula that appears in the sheet (and modified it to my cell ranges, sheets, etc.):

=INDEX($B$2:$B$71,MATCH(E2,$A$2:$A$72,0))

I didn't use this formula which also appears in the sample spreadsheet:

=IFERROR(INDEX($C$2:$C$80,MATCH(G2,$A$2:$A$106,0)),"")

I'm wondering why there are two different formulas? Do I need to use them both? Because it seems to be working with just the first formula.

Thank you again so much for your time and help! 🙂
Diane

[#7]

HIi @DianeDennis 

Both formulas are the same and will produce the same result.

The only difference is that we usually wrap any formula with IFERROR() to handle errors in the formulas. IFERROR() returns a value that we specify if a formula evaluates to an error; otherwise, it returns the result of the formula.

In our example, if we do not wrap the first formula with IFERROR() it may give #N/A.

IFERROR() gives us the option to replace #N/A or other types of errors with 0, space, blanks or any value that we may think is suitable.

Thanks

Tauqeer

[#8]

Hi @tauqeeracma!

Thank you so very much!! I'm gonna be dangerous now!

Have a great day!!
Diane