Is there a shorter way to use an array to look up multiple values?

I have a spreadsheet that lists the same part number multiple times. I am using arrays to find the rows on another tab that contain that part number. I am ultimately trying to find cell values in those rows.

Please Login or Register  to view this content.

I replace $1:$1 with $2:$2 in the next cell to find the second row that contains that part number, etc, etc. From that, I want to take the row references and put them in a formula to find the inventory locations for the part number in those given rows. Am I going about this the long way? Can I combine both steps in 1 formula?

The tab that contains the array formulas is titled "FINAL". The tab that contains the rows (and ultimately the inventory locations I am looking for) is titled "QOH ALL". The part numbers are in column A on both tabs. The inventory locations are in column D on the QOH ALL tab.

I hope this makes sense.

That is what I did. I was just wonderinfg if there was a way to combine the steps.

Now I'm having an issue incorporating the resulting row number into the INDEX formula. Will you please provide an example?

Thanks! I figured out my issue with INDEX.