Nov 30 2021 02:46 AM
Hi everyone!
Hope you are all well. I am just trying to get more in depth with Excel and I am currently facing a problem which would make my life a lot easier if acomplished.
The logic of the problem is:
If sheet 1 Column A value is = to Sheet 2 Column B then:
Sheet 1 Column B value is populated to Sheet 2 Column C
Sheet 1 Column C value is populated to Sheet 2 Column D
Basicaly if the values match i want it to populate with extra information in Sheet 2.
What function should I be using? The sheets are both on the same workbook.
I appreciate any feedback.
J
Nov 30 2021 04:27 AM
Let's say the data begin in row 2.
In Sheet 2, cell C2:
=IFERROR(VLOOKUP(B2, 'Sheet 1'!$A$2:$C$10000, 2, FALSE), "")
And in D2:
=IFERROR(VLOOKUP(B2, 'Sheet 1'!$A$2:$C$10000, 3, FALSE), "")
Change Sheet 1 to the actual name of the first sheet.
If the first sheet has more than 10000 rows of data, increase 10000 in the formulas.
Select C2:D2, then fill down.
Nov 30 2021 06:45 AM
Nov 30 2021 07:09 AM
VLOOKUP(B2, 'Sheet 1'!$A$2:$C$10000, 2, FALSE)
takes the value of B2 on Sheet 2 and searches for it in the first column of 'Sheet 1'!$A$2:$C$10000, i.e. in column A of Sheet 1. If it finds a match, it will return the value from the 2nd column in that range, i.e. column B on Sheet 1. The fourth argument FALSE specifies that we're looking for an exact match.
If no match is found, VLOOKUP will return #N/A. This is suppressed by using IFERROR - it replaces the error value with an empty string "".