Search a Row and return the column value

[Shockwiz_rentals_UK]

Hello

First post so here I go...

I have a customer who has a unique identifier (ACODE) and a list of serial numbers in separate columns.

I want to search for a serial number and then know which customer ACODE owns that serial number.



[TABLE="width: 383"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col span="2" style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]ACODE
[/TD]
[TD="align: center"]Serial numbers[/TD]
[TD="align: center"]Serial numbers[/TD]
[TD="align: center"]Serial numbers[/TD]
[/TR]
[TR]
[TD="align: center"]A123160[/TD]
[TD="align: center"]436175[/TD]
[TD="align: center"]436145[/TD]
[TD="align: center"]271432[/TD]
[/TR]
[TR]
[TD="align: center"]A145906[/TD]
[TD="align: center"]699629[/TD]
[TD="align: center"]167777[/TD]
[TD="align: center"]698981[/TD]
[/TR]
[TR]
[TD="align: center"]A145537[/TD]
[TD="align: center"]170820[/TD]
[TD="align: center"]170819[/TD]
[TD="align: center"]168556[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance

 

[steve the fish]

See if you can use this:

=INDEX($A$2:$A$4,AGGREGATE(15,6,(1/($B$2:$D$4=G1)*ROW($B$2:$D$4))-ROW($A$2)+1,1))

Table in A1:D4 and a serial number to search for in G1.

 

[Shockwiz_rentals_UK]

That worked! You are a genius!

Thank you so much