Forum Discussion

Deleted's avatar
Deleted
Jan 13, 2018

Compare two worksheets and update any empty cells

Table 1                                  Table 2

A                  B                       A              B

URN           Postcode           URN       Postcode

12345          DE34                12345       DE34

Blank Cell     BB35               34567       BB35

126788        FK45                126788      FK45

 

Hi, my goal is to search table 2 for postcode (column b) find a match and update table 1 column a based upon value in column a in table 2 IE table 1 URN Column A Row 2 would be populated with 34567. 

 

I have tried vlookup and a few other bits im not sure can anyone suggest a  formula and give examples?

Thanks

 

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    Alex,

     

    In this case, VLOOKUP won't work, because the lookup value isn't in the leftmost of the table array, this a limitation in VLOOKUP.

     

    So, you can use INDEX/MATCH which is the best alternative.

     

    =INDEX($D$2:$D$4,MATCH(B2,$E$2:$E$4,0))

     

     

    Please find the attached file.

     

Resources