Aug 16 2019 10:29 AM
Aug 16 2019 10:29 AM
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)
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!!
Aug 16 2019 12:56 PMSolution
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.
Aug 16 2019 01:42 PM
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!
Aug 16 2019 01:49 PM
Nov 10 2022 07:57 AM
Nov 10 2022 09:38 AM
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.
Nov 10 2022 11:41 AM