If value = value then populate rows

%3CLINGO-SUB%20id%3D%22lingo-sub-3017615%22%20slang%3D%22en-US%22%3EIf%20value%20%3D%20value%20then%20populate%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3017615%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20you%20are%20all%20well.%20I%20am%20just%20trying%20to%20get%20more%20in%20depth%20with%20Excel%20and%20I%20am%20currently%20facing%20a%20problem%20which%20would%20make%20my%20life%20a%20lot%20easier%20if%20acomplished.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20logic%20of%20the%20problem%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20sheet%201%20Column%20A%20value%20is%20%3D%20to%20Sheet%202%20Column%20B%20then%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%201%20Column%20B%20value%20is%20populated%20to%20Sheet%202%20Column%20C%3C%2FP%3E%3CP%3ESheet%201%20Column%20C%20value%20is%20populated%20to%20Sheet%202%20Column%20D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasicaly%20if%20the%20values%20match%20i%20want%20it%20to%20populate%20with%20extra%20information%20in%20Sheet%202.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20function%20should%20I%20be%20using%3F%20The%20sheets%20are%20both%20on%20the%20same%20workbook.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20any%20feedback.%26nbsp%3B%3C%2FP%3E%3CP%3EJ%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3017615%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3018011%22%20slang%3D%22en-US%22%3ERe%3A%20If%20value%20%3D%20value%20then%20populate%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3018011%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1233406%22%20target%3D%22_blank%22%3E%40jcol7884%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20data%20begin%20in%20row%202.%3C%2FP%3E%0A%3CP%3EIn%20Sheet%202%2C%20cell%20C2%3A%3C%2FP%3E%0A%3CP%3E%3DIFERROR(VLOOKUP(B2%2C%20'Sheet%201'!%24A%242%3A%24C%2410000%2C%202%2C%20FALSE)%2C%20%22%22)%3C%2FP%3E%0A%3CP%3EAnd%20in%20D2%3A%3C%2FP%3E%0A%3CP%3E%3DIFERROR(VLOOKUP(B2%2C%20'Sheet%201'!%24A%242%3A%24C%2410000%2C%203%2C%20FALSE)%2C%20%22%22)%3C%2FP%3E%0A%3CP%3EChange%20Sheet%201%20to%20the%20actual%20name%20of%20the%20first%20sheet.%3C%2FP%3E%0A%3CP%3EIf%20the%20first%20sheet%20has%20more%20than%2010000%20rows%20of%20data%2C%20increase%2010000%20in%20the%20formulas.%3C%2FP%3E%0A%3CP%3ESelect%20C2%3AD2%2C%20then%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

 

3 Replies

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

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

@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 "".