Index/Match Formula with two Criteria

[clari55a]

Hello, I am totally stuck with an INDEX/MATCH, and am hoping for some support, please.

In the attached sample workbook, the 'Info' worksheet contains information about products. 'Sheet1' contains a table that I need to complete, drawing data from the 'Info' worksheet.

Columns A:C in 'Info' contain a SKU code, which is also listed in the table in 'Sheet1'.

I need a formula that looks at the SKU code listed in column F of 'Sheet1' and then looks at the various headers in row 1, and finds the appropriate data from the 'Info' worksheet. For example, it sees the SKU Code 'CP83B5' in cell F4, and brings back a result of '396' in to cell G4, and '1683' in to cell H4, etc.

In the attached sample, I have attempted to solve this in cell G8 of 'Sheet1' but it doesn't work. Any support, please? Many Thanks

[XLent]

given your SKUs do not repeat in every row (as you're merging cells) you would need to adjust approach and, further assuming the column headers could be modified from one day to the next, you could use something like

Please Login or Register  to view this content.

note however your header values in row 1 would need to mirror the values used in Col N on Info - else you would need to adjust to wildcard based MATCH.

[wk9128]

worksheet Or Tab name : Sheet1

Cell G4 formula , Drag down and across

Please Login or Register  to view this content.

[menem]

Please try

G4
=IFERROR(INDEX(Info!$O$3:$O$43,AGGREGATE(14,6,(ROW(Info!$A$2:$A$33)-ROW(Info!$A$2))/(Info!$A$2:$C$43=$F4),1)+MATCH("*"&G$1&"*",Info!$N$3:$N$43,0)-1),"")

Regards.