Index Match or IF Nested Formulas need most efficient Way Formula

[daleholden]

Please, see attached worksheet to explain what I am looking to achieve.

Also, do the headers in Data row have to match the dropdown box?

 

[James006]

Hi,

In cell C2 ... you could have

=INDEX(Table1[#All],MATCH(Table2[[#This Row],[sku]],Table1[[#All],[sku]],0),MATCH(Table2[[#This Row],[store]],Table1[#Headers],0))

Hope this will help

 

[AlanY]

same idea but a slightly user friendly version, you should adjust the range to suit for more efficient, e.g. Data!A1:E1000, etc

=INDEX(Data!A:E,MATCH($A2,Data!A:A,0),MATCH($B2,Data!$1:$1,0))

 

[daleholden]

HI Alan

That works great but i need to know is it possible to achieve this if the title headers do not match the dropdown?
I know it is a stupid question but Store1 has many headers Eg Store1 Price, Store 1 VAT Excl, Store 1 VAT Owed Store1 FVF, Store1 PP FVF.
So the drop down has many cells pointing at it a recovering data from various Store 1 headers.
I cannot change the dropdown name so is it possible to hardcode $B2 like "Store 1 FVF"
?

 

[AlanY]

HI Alan

That works great but i need to know is it possible to achieve this if the title headers do not match the dropdown?
I know it is a stupid question but Store1 has many headers Eg Store1 Price, Store 1 VAT Excl, Store 1 VAT Owed Store1 FVF, Store1 PP FVF.
So the drop down has many cells pointing at it a recovering data from various Store 1 headers.
I cannot change the dropdown name so is it possible to hardcode $B2 like "Store 1 FVF"
?

do you mean e.g. "Store 1 FVF" or "Store 1 Price" in Sheet 1, you still want to pick the price in the "Store 1" column?

 

[daleholden]

HI

The drop down has to stop the same if possible so when i select Store 1 in my main data sheet Store 1 has 6 different columns which start with Store 1 but appended with FVF , VAT , VAT Excl as do the other store numbers.
SO my selection in the drop down tells other cells to go off to other areas starting with Store 1

Hope that makes sense

 

[AlanY]

is your data sheet looks something like this?

Book1
	A	B	C	D	E	F	G	H	I	J
1		Store 1 FVF	Store 1 VAT	Store 1 VAT Excl	Store 1 etc1	Store 1 etc2	Store 1 etc3	Store 2 FVF	Store 2 VAT	Store 2 VAT Excl
2	6789	10	20	30	40	50	50	50	50	50
3	12345	110	120	130	140	150	150	150	150	150
Data

if so, the drop down menu on the summary sheet needs to match the datasheet titles to work.

if the drop down menu only offer Store 1, Store 2 etc how does excel knows which Store 1 column (price) to pick?

back to your question of course you can replace $B2 with the column title but that will be for every single row!

 

[AlanY]

if the drop down menu doesn't has the full list of the column headers on the datasheet, one option is to clear the existing validation list and create a new one with all the title headers on the datasheet

 

[daleholden]

HI

I think i am struggling to explain it correctly

Store 1 feeds 5 other cells that does a different lookup and calculations based on Store 1.

SO this is a current formula i am using =IF(B6="Website",0,IF(B6="eBay",IFERROR(@INDEX('[Anditsgone MASTER eBay and Amazon Sheet.xlsm]AG eBay'!$A:$V,MATCH(A6,'[Anditsgone MASTER eBay and Amazon Sheet.xlsm]AG eBay'!$A:$A,0),13),IF(B6="SC eBay",IFERROR(@INDEX('[Anditsgone MASTER eBay and Amazon Sheet.xlsm]AG eBay'!$A:$V,MATCH(A6,'[Anditsgone MASTER eBay and Amazon Sheet.xlsm]AG eBay'!$A:$A,0),14),*'[Anditsgone MASTER eBay and Amazon Sheet.xlsm]Sales'!$G6)

 

[daleholden]

HI

I think I am struggling to explain it correctly

Store 1 feeds 5 other cells that does a different lookup and calculations based on Store 1.

SO this is a current formula i am using =IF(B6="Website",0,IF(B6="eBay",IFERROR @Index('[Anditsgone MASTER eBay and Amazon Sheet.xlsm]AG eBay'!$A:$V,MATCH(A6,'[Anditsgone MASTER eBay and Amazon Sheet.xlsm]AG eBay'!$A:$A,0),13) @Index('[Anditsgone MASTER eBay and Amazon Sheet.xlsm]Own Stock'!$A:$N,MATCH(A6,'[Anditsgone MASTER eBay and Amazon Sheet.xlsm]Own Stock'!$A:$A,0),14)),IF(B6="SC eBay",IFERROR @Index('[Anditsgone MASTER eBay and Amazon Sheet.xlsm]AG eBay'!$A:$V,MATCH(A6,'[Anditsgone MASTER eBay and Amazon Sheet.xlsm]AG eBay'!$A:$A,0),14) @Index('[Anditsgone MASTER eBay and Amazon Sheet.xlsm]Own Stock'!$A:$N,MATCH(A6,'[Anditsgone MASTER eBay and Amazon Sheet.xlsm]Own Stock'!$A:$A,0),14)),IF(B6="Amazon",IFERROR @Index('[Anditsgone MASTER eBay and Amazon Sheet.xlsm]Amazon'!$A:$R,MATCH(A6,'[Anditsgone MASTER eBay and Amazon Sheet.xlsm]Amazon'!$A:$A,0),13) @Index('[Anditsgone MASTER eBay and Amazon Sheet.xlsm]Own Stock'!$A:$U,MATCH(A6,'[Anditsgone MASTER eBay and Amazon Sheet.xlsm]Own Stock'!$A:$A,0),21)))))*'[Anditsgone MASTER eBay and Amazon Sheet.xlsm]Sales'!$G6)

I no longer have two sheets Own Stock or Amazon sheet so I was trying to deconstruct this to remove these two sheets.
At the same time i was trying to see if it was possible to make this more efficent.
But the dropdown list cannot be changed really