Dec 21 2021 10:10 AM
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.
Dec 21 2021 12:17 PM
In E2:
If you have Office 2021 or Microsoft 365:
=XLOOKUP(D2,$J$2:$J$7,$I$2:$I$7,"")
In any version:
=IFERROR(INDEX($I$2:$I$7,MATCH(D2,$J$2:$J$7,0)),"")
Fill down to E11.
Dec 22 2021 05:09 AM
@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
Dec 22 2021 06:36 AM