CODEWITHSUNDEEP
excelexcel-formula
Jordan Mynes
Jordan Mynes

Reputation: 3

Index Match Formula issue

I have a list of SKU's from left to right columns B - X and 400 rows, not all of the cells are populated so there are plenty of blank cells.

I then have the product name in column Y (as there can be multiple colour variants and SKUs for 1 product).

On a separate tab I have all the order line details, which includes SKU, I need to append the product name on to the end of this using the first tab.

I have tried Index/Matching but it doesnt seem to be working, vlookup doesnt as I need to bring back the same column (Y) irrelevant of where it finds the match.

=INDEX('All Lines'!Y3:Y428,match(*Product SKU*,'All Lines'!B3:X428,0))

All lines is my first tab, with the product SKU coming from the tab with the order details

Expectation is the product name will be output (which is in column Y)

Upvotes: 0

Views: 45

Answers (1)

JvdV
JvdV

Reputation: 75840

At first I misinterpreted your question I think. Are you looking for a certain value that can be anywhere in the matrix B3:X428 and return the row to the INDEX() row parameter?

In that case you could try to incorporate SUMPRODUCT() within the INDEX() row parameter like so (simplified):

enter image description here

The formula in G2:

=INDEX(D2:D9,SUMPRODUCT((A2:C9=G1)*ROW(A2:C9))-1)

Upvotes: 1

Related Questions