Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
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 '09 at 6:26
    
Sorry; I had a slight mistake which Col pointed out. Try it now. –  Isaac Moses Sep 4 '09 at 13:24
    
Just try it, work. :) –  EK Sep 7 '09 at 2:57

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)
      )
share|improve this answer
    
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. –  Isaac Moses Sep 4 '09 at 13:22
    
Good point. I guess I'd better amend mine too. –  Col Sep 5 '09 at 13:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.