How to make an IF FUNCTION recur on subsequent rows if it fails.

Hi,

I'm hoping someone can help me to resolve the following. I have two columns as per below:

Number 1 Name 1
Number 2 Name 2
Number 3 Name 3
Number 4 Name 4
Number 5 Name 5
Number 6 Name 6
Number 7 Name 7
Number 8 Name 8
Number 9 Name 9
Number 10 Name 10
Number 11 Name 11


I also have a cell (M1) that utilises a data validation list in order to output a user chosen name from the "Name's" column. When a name (eg. Name 4) is chosen, I would like another cell (N1) to output the associated Number (Number 4).

I was using an IF formula as per below:

=IF(M1 = CELL1, CELL2, "")
* Cell 1 is J38 and refers to the cell relevant to "Name 1" and Cell 2 is F38 equals the cell relevant to "Number 1"

However, obviously this would only return the first row as if the test fails, it doesn't continue down through rows 39 and onwards.

Thank you!

Thank you that worked perfectly!!! Cheers for the best practice advice as well; I'll take that into account next time