Where text in one cell matches text another cell populate the value in the adjacent cell

New Contributor

I am trying to create a formula that when I paste text in to blank cells D2:D11 the cells adjacent (E2:E11) contain a formula that checks cells J2:J7 and if the text in D2 matches any data in cells J2:J7 (there will never be a duplicate entry in J2:J7 or I2:I7) it will auto populate the number contained in the adjacent cell (I) into the corresponding cell E2:E11. For example where D1 = Glasgow FC it checks J2:J7 matches with J4 and populates the figure at I4 (3) into E2 when the formula is copied into the other E cells it will also populate I4 (3) into E5 and E8.

3 Replies


In E2:

If you have Office 2021 or Microsoft 365:


In any version:


Fill down to E11.

@Hans Vogelaar many thanks that has worked a treat. Much appreciated.


It is interesting that the formula works perfectley in the same worksheet but if the list of look up data is contained in an adjoining spreadsheet (same workbook) it doesnt.


Many thanks and Merry Christmas


It should work across sheets too:


See the attached sample workbook.