Formula

Lesa

I have a sheet that has a supplier number in one cell that is assigned to a supplier in another cell. Can I write a formula so that when the supplier's name is entered in one cell their supplier number automatically populates another cell on the same row?

Adam Murphy

Yes you can use an INDEX/MATCH or an INDEX/COLLECT formula to do this, for example something like =INDEX(COLLECT({Supplier Number}, {Supplier Name}, [Supplier Name]@row)) should work.

Paul Newcome

@Lesa I would suggest an IDNEX/MATCH like so:

=INDEX({Supplier Number}, MATCH([Supplier Name]@row, {Supplier Name}, 0))

@Adam Murphy You missed the "row number" argument for your INDEX function. Your formula as is will throw an error.

=INDEX(COLLECT({Supplier Number}, {Supplier Name}, [Supplier Name]@row), 1)

Adam Murphy

@Paul Newcome Ah, I was going quickly, my bad. Sorry for the confusion!