Index Match Help please

I am trying to use index match to return a descriptive value from a range, I am either doing it wrong or using the wrong method for my needs.

I have a sheet named SKU that holds the name of the client in Row 1 beneath the clients name is a list of SKU codes the next column holds the description of the SKU codes which is what I want to return the value of

I have a sheet named PRINT NOTE with the name of the client in cell B1 and the name of the SKU in cell A20.

If I use the following match reference =match($B$1,SKU!$C$1:$AR$1,0)
B1 = Multipower
B1 = BOA
B1 = CCE_Monster

Then it returns the correct values i.e.
1 for Multipower
3 for BOA
7 for CCE Monster

IndexMatach.JPG

I now want to find the relevant SKU description.

Exp 1
On sheet “PrintNote”
Cell B1 contains ‘CCE_Monster’
Cell A20 contains ‘G9’
Cell B20 contains the Index Match formula to return the description required

I therefore want the value in J7 to be returned to B20 I appreciate that I have found it is in the 7th column and now I want to count down 7 rows and return the value 1 to the right, but I can’t get the formula right

Exp 2
On sheet “PrintNote”
Cell B1 contains ‘Mars_Chillers’
Cell A20 contains ‘DEIMOS’
Cell B20 contains the Index Match formula to return the description required

I therefore want the value in H5 to be returned to B20

Any help would be much appreciated.

Many thanks

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on REPLY then GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thank you for your responses, i have attached an example as requested.

What i am trying to achieve is to find the locate the cell in the row 1 of the SKU
tab that relates to the value held in cell A5 of the PrintNoteTest tab. In this example this is 'ClientD'

ClientD is a named range in the SKU tab =SKU!$I:$I

I then want it to match this to the SKU value held in A20, in this case "VD1544" and return the value held
in the next field to the right, similar to a vlookup.

VD1544 is in the 11th row in the range ClientID.

I have displayed the results that i need in 'PrintNoteResult' I am aware that my index match is wrong in
my test tab and i probably need to use a count somewhere.

I hope this is clearer

Many thanks

Pros

Hi Phil, thanks for the input, the issue is that 'ClientD' is a variable based on the value returned to cell A5, therefore if 'ClientE' was in A5 and SKU 'CE041332A' was enrered into A20 your formula would work.

Sory, my mistake.
Try this instead

Please Login or Register  to view this content.

Originally Posted by philaugust2004

Sory, my mistake.
Try this instead

Please Login or Register  to view this content.

This works perfectly, thanks. I have adapted the model to work with google sheets, now i'm going to try to understand what this actually is doing. Thanks again

Originally Posted by FDibbins

Try this beast, copied down...
=if(A20="","",INDEX(OFFSET(SKU!$C$1,0,MATCH($A$5,SKU!$C$1:$L$1,0),COUNTA(ClientA),1),MATCH($A20,OFFSET(SKU!$C$1,0,MATCH($A$5,SKU!$C$1:$L$1,0)-1,COUNTA(ClientA),1),0)),"")

Thanks for your input.