Index/Match with Varied Offset

Question

Running into some trouble performing an Index/Match where the offset rows could be spaced 1 row apart, 2 rows apart, or 3 rows apart. Below is an example of the setup:

Sheet1:

|   A   |   B   |   C   |   D   |   E   |   F   |
-------------------------------------------------
|       |       |       |       | Apple |       |
-------------------------------------------------
| Ser1  |       |       |       |       |       |
-------------------------------------------------
|       |       |       |       | Orange|       |
-------------------------------------------------
| Ser2  |       | Ser3  |       | Ser4  |       |
-------------------------------------------------
| Ser5  |       |       |       |       |       |

Sheet2:

|   A   |
---------
| Ser1  |
---------
| Ser2  |
---------
| Ser3  |
---------
| Ser4  |
---------
| Ser5  |

I have a list of the serial numbers (ser1, ser2, etc) in another sheet, and I need to match values in Column E where the correct value is above the serial number by 1, 2, or 3 rows. As you can see, serial numbers could be in column A, C, or E.

Ex: Ser1 should match on Apple. Ser2, Ser3, ser4, and Ser5 should match on Orange.

I can't seem to figure out the correct Index/Match that work completely since the offset at the end of the formula is either + or - by a static row number.

I was hoping to not use VBA only because I'm not very good at it. — dgibbons82
This is just an absolutely awfully setup spreadsheet. Rather than trying to figure out a complicated formula to do what you want, you should set up your spreadsheet in a more user-friendly way. — ImaginaryHuman072889
I agree 100% but I have no control to how the client sends me the data. If I had my way, they'd send over a db export setup exactly as I need it, but that's often not an option. — dgibbons82

TomJohnRiddle TomJohnRiddle · Accepted Answer · 2018-04-27T14:56:49

Rough solution:

In your sheet Make a structure like this:

|   A   |   Row  | Lookup
---------
| Ser1  |
---------
| Ser2  |
---------
| Ser3  |
---------
| Ser4  |
---------
| Ser5  |

For column Row put:

=SUMPRODUCT((Sheet1!A$1$:E$5$=A2)*ROW(Sheet1!A$1$:E$5$))

this will calculate row in which serial number occures in your data range. Then you may put in Lookup column:

=LOOKUP(2,(1/(INDEX(Sheet1!$E$1:$E$5,1,1):INDEX(Sheet1!$E$1:$E$5,B2-1,1)<>"")),Sheet1!$E$1:$E$5)

This formula will lookup for last non-empty cell in column E (please refer here for more comments) in range that is above selected serial number.

This is a partial solution as for "Ser5" you will receive "Ser4". To overcome that issue you may perform additional Vlookup on achieved results.

Index/Match with Varied Offset

1 Answers