2

My excel data has 2 columns. Data in my worksheet is like below.

ColumnA   ColumnB
A       | Data of A
B       | Something
C       | Some text
D       | Test Data
E       | Data of E

I want to know how can I set value of columnB when I input value to columnA. E.g. if I have input new row and input columnA = D then the value of columnB is = "Test Data" automatically, but if my input has never found before it does nothing.

2 Answers 2

2

I think what you want in B2 (and copied below) is:

IF(
   ISNA(
        MATCH(A2, A$1:A1, 0)
       ),
   "",
   VLOOKUP(A2, A$1:B1, 2, FALSE)
  )

If there's no previous match with A2, MATCH will return #NA, so ISNA will return TRUE, so the IF will yield the first value, "", or nothing.

If there is a previous match, MATCH will not return #NA, so ISNA will return FALSE, so the IF will yield the result of the VLOOKUP. VLOOKUP will find the row whose A value exactly matches A2 and return the value in the second column.

3
  • from my sample data if I input 'E' at first column of the new row, it don't show "Data of E" at the second column.
    – EK
    Sep 4, 2009 at 6:26
  • Sorry; I had a slight mistake which Col pointed out. Try it now. Sep 4, 2009 at 13:24
  • Just try it, work. :)
    – EK
    Sep 7, 2009 at 2:57
1

I think moe37x3 made some typos, try this formula (moes was only checking in cell a1 for a match and didn't have the result section in his vlookup

IF(
       ISNA(
            MATCH(A2, A$1:A1, 0)
           ),
       "",
       VLOOKUP(A2, A$1:B1, 2, FALSE)
      )
2
  • My bad for leaving out column B. Yours has an issue, too, though, in that you include all of column B, which includes the cell you're putting the formula in, which results in a circular reference. I'm going to fix my solution per your comment now. Sep 4, 2009 at 13:22
  • Good point. I guess I'd better amend mine too.
    – Col
    Sep 5, 2009 at 13:49

You must log in to answer this question.