Return row & column number from cell in 2d array

Hi Guys & gals,

I should say firstly that I've tried searching for a solution within the forum but with no luck, although that could just be my searching!

I have a sheet (attached) that collates all of the pit stops during a motor race. A2:A27 = race positions, B1:AY1 = lap, the table itself contains the car numbers.

I would like to be able tabulate the point at each car made its FIRST stop (cars could make more than one). i.e. Enter the car number into cell D29 and have the position (D30) and lap pit stop occurred (D31) returned.

initially I thought that this was a simple problem but lots of google searches later suggests otherwise. Any advice would be much appreciated.

RanaldPitStops_01.xlsx

Hi Sixthsense,

That isn't quite what I need. Rather than have two inputs (D30 & D31 in your equation) I would like to search using just one, the car number (entered into D29).

So, for instance if I were to enter 15 into cell D29 I would like D30 = 8 (position) and D31 = 11 (lap)

Thanks,

Ranald

That's great bu not quite what I need. I really would like to tabulate the results for each car# as a quick lookup.

The final suggestion in your previous suggestion works however Excel gives up and returns an 'Excel ran out of resources' message when I place the equation in the full workbook (PitStops_1 is just an example). Do you have any suggestions as to how I can over come this by reducing/simplifying the following?

Please Login or Register  to view this content.