1

I have data in cells A1-A50 and also data in C1-C50.  Columns A and C are synchronized; i.e., the value in Cn corresponds to the value in An.

When I enter information in column A (new row, A51+) I want Excel to automatically fill in data for column C if what I entered in column A is also present above, using the corresponding data from C for that row.

Example:

  A      B      C
John          blueberry
Phil          apple
David         banana
John
David

I want Excel to fill in "blueberry" and "banana" in column C for the last two rows.

Improve this question
2
  • 1
    Your question is little confusing basically John Blue, Phil Red indicates Conditional Formatting but i want excel to 'see' that this information is the same as any of the row A1-A51, and to automatically fulfill it in column C is quite different !!
    – Rajesh Sinha
    Commented Aug 12, 2018 at 9:27
  • 1
    Your question is really unclear. I have proposed an edit, please approve it if I got your question right.
    – confetti
    Commented Aug 12, 2018 at 9:30

3 Answers 3

Reset to default
2

Paste below formula:

=INDEX($C$1:$C$3,MATCH($A4,$A$1:$A$3,0))

to cell C4, then copy for the rest of list

Improve this answer
1

Paste this formula into the first empty cell in column C (in this case, at row 4):

=IF(ISNUMBER(MATCH(A4;A$1:A3;0));VLOOKUP(A4;A$1:B3;3;FALSE))

(Modify "A4" in case you are on a different row).

Drag down to fill the empty cells below. It returns FALSE if there's no matching value in A.

Improve this answer
2
  • But this will just display blanks and FALSE cells. The OP wants to see corresponding values from Column C.
    – Scott - Слава Україні
    Commented Aug 12, 2018 at 14:49
  • You're right - I edited the formula to account for C and not B.
    – Dimitrios Papadakis
    Commented Aug 13, 2018 at 15:04
0

enter image description here

How it Works:

  1. Write this formula in Cell B7 & fill down.

    =IFERROR(VLOOKUP(A7,A3:B5,2,FALSE),"No Match")

  2. You get Scenario Before New Data.

  3. Now enter desire Name in A7 and after.

  4. Finally you get Scenario After New Data.

Note:

  1. Adjust the cell references in formula as needed.
  2. You may replace text No Match in formula with any text of your choice or keep it blank.
Improve this answer
1
  • 1
    what is wrong with this answer ?? Since filling "blueberry" and "banana" in column C for the last two rows, as OP says !!
    – Rajesh Sinha
    Commented Aug 14, 2018 at 3:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .