Forum Discussion

Copper Contributor

Nov 30, 2021

If value = value then populate rows

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 th...

excel

Formulas and Functions

Macros and VBA

HansVogelaar

MVP

Nov 30, 2021

jcol7884

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.

jcol7884
Copper Contributor
Nov 30, 2021
Hi Hans,
Thank you for the helpful answer.
Would you be able to breakdown the formula after VLOOKUP so I can understand it generically?
Very new to the power of Excel and this will help a lot.

Thank you.
J
- HansVogelaar
  MVP
  Nov 30, 2021
  jcol7884
  
  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 "".