Lookup a specific instance

[burlywood66]

I know this question has been asked repeatedly, but I can't apply it to my situation without creating an error.

I want to lookup a specific instance of a value on a different sheet. The following is my situation:


[Sheet 1, Columns B through I] contain where I want return values (return first instance in B, second in C, etc.)

[Sheet 1, A2 and down] contain lookup values

[Sheet 2] contains the values i want to show on [Sheet 1]

What formula do I need to put in [Sheet 1, B2 and over and down] to return the specific instance.

Thanks so much - let me know if you need more information.

[JBeaucaire]

As long as the 8 matching values are always together in Sheet2, you can put this in B2 and copy down/across:

=INDEX(Sheet2!$B:$B, MATCH($A2, Sheet2!$A:$A, 0) + COLUMN(A$1)-1)


If your sheet2 column A is not organized, if it's a mixed up listing, you'll need a custom function (UDF) for that to be easy.