Index and Match

[SteveRA]

Hi

I have, with the amazing help with somebody on this forum, managed to come close to getting a formula to work but I cannot put the final pieces together. I have the following formula

=INDEX(CCPInventory!$A$1:$Z$5000,MATCH(O29,CCPInventory!B:B,0),MATCH("VAR_CostPrice_Net",CCPInventory!$A$1:$Z$1,0))

The problem with this is that the O29 and the CCPInventory!B:B are hardcoded which wont work. The formulas I have are:

=INDEX($A$1:$DD$5000,0,MATCH("External ID",$A$1:$Z$1,0)) returns the correct value for O26 but if I copy and paste this formula over the O26 in the first formula I get an #N/A
and
=MATCH("VAR_SKU",CCPInventory!$A$1:$Z$1,0) which returns 2. Firstly this range is on another sheet "CCPInventory" and secondly I have tried using the address function to give me the column letter but this gives me the address of a cell and not the entire column.

This entire formula has no explicit references since all cols are constantly in different places so I have to create the entire statement based on the column headers hence the reason for so many match statements.

Any and all help would be appreaciated.

Thanks
Steve

 

[pbornemeier]

What cell is the first formula in?
What cells do you want to copy the formula to?
What do you mean by " all cols are constantly in different places so I have to create the entire statement based on the column headers "

"This entire formula has no explicit references" is not correct the $$ make the formulas refer to specific cells no matter where the formula is placed.

If you want to extract the column from an address use:
=COLUMN(O26)
which will return 15
which can be put in the =ADDRESS function for further use.

 

[SteveRA]

Hi Phil

"What do you mean by " all cols are constantly in different places so I have to create the entire statement based on the column headers " "
My data is 100% dynamic in that it comes from different sources and based on the users preferences can be displayed in any order within a worksheet. The only constant in the multiple worksheets is the column names.

"What cell is the first formula in? "
Because everything is based on the users preferences this can be anywhere. This is why this formula should be able to be placed in any cell because it references by the column headers and not a specific cell.

"What cells do you want to copy the formula to? "
This same function will be used thoughout the worksheet based on column header criteria. For example I have a parameters worksheet that says the user wants a cost price in column x and the rest of the parameters tell me which column headers I need to use to get that cost price. In my example I have used VAR_SKU , EXTERNAL ID and VAR_CostPrice_Net as column headers but these can be anything. The formula will be inserted into the correct place using a vba script which will insert the necessary column headers into the formula as it builds it.

I cant use the COLUMN function because this gives me a number. I already have the number created by the Match function so I need something to do the reverse of the column function. ie turn 2 into B.

Hope this makes sense.

Steve

 

[pbornemeier]

Use
=SUBSTITUTE(ADDRESS(1,2,4,1),"1","")
To turn 2 into B.

If your data table always starts in A1 then you can use a named range to define it:
Name: DynData
RefersTo: =OFFSET(CCPInventory!$A$1,0,0,COUNTA(CCPInventory!A:A),COUNTA(CCPInventory!1:1))
and use the named range as part of your formula instead of a hard-coded area

"I have a parameters worksheet that says the user wants a cost price in column x and the rest of the parameters tell me which column headers I need to use to get that cost price. "
What addresses in the parameters worksheet correspond to in what values in your formula?

 

[SteveRA]

Thanks Phil

The substitute works like a dream but Im starting to think it is the syntax of the entire statement that is making it fail..

In my mind this is the full formula where the red is the column headers where the data is comming from. The "External ID" is on the current sheet but the other two are on a different sheet which is causing the address statement to go wonky.

=INDEX(CCPInventory!$A$1:$Z$5000,MATCH(INDEX($A$1:$DD$50000,0,MATCH("External ID",$A$1:$Z$1,0)),[[[NEED TO PUT CCPInventory! AS A SHEET NAME HERE]]SUBSTITUTE(ADDRESS(1,MATCH("VAR_SKU",CCPInventory!$A$1:$Z$1,0),4,1),"1",""),0),MATCH("VAR_CostPrice_Net",CCPInventory!$A$1:$Z$1,0))

Any suggestions as to what I am doing wrong?

Steve

 

[pbornemeier]

Do the headers in each sheet correspond with each other? That is the column "External ID" in CCPInventory is in the same column as the column "External ID" in "a different sheet " ?

What is the name of the sheet where the formulas will be?
Is there another sheet involved besides that one and the CCPInventory worksheet?

The INDEX function has 3 (or 4) arguments. What parts of
INDEX(CCPInventory!$A$1:$Z$5000,MATCH(INDEX($A$1:$DD$50000,0,MATCH("External ID",$A$1:$Z$1,0)),[[[NEED TO PUT CCPInventory! AS A SHEET NAME HERE]]SUBSTITUTE(ADDRESS(1,MATCH("VAR_SKU",CCPInventory!$A$1:$Z$1,0),4,1),"1",""),0),MATCH("VAR_CostPrice_Net",CCPInventory!$A$1:$Z$1,0))
Goes to each part of the first INDEX formula
Reference: CCPInventory!$A$1:$Z$5000
row_num
column_num
area_num

The second INDSEX formula:
Goes to each part of the first INDEX formula
Reference: $A$1:$DD$50000
row_num
column_num
area_num

"I have a parameters worksheet that says the user wants a cost price in column x and the rest of the parameters tell me which column headers I need to use to get that cost price. "
What addresses in the parameters worksheet correspond to in what values in your formula?