# Index Match or IF Nested Formulas need most efficient Way Formula

#### daleholden

##### Board Regular
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?

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### James006

##### Well-known Member
Hi,

In cell C2 ... you could have

Code:
``=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

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member

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

##### Board Regular
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

##### Well-known Member

is your data sheet looks something like this?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Store 1 FVF</td><td style=";">Store 1 VAT</td><td style=";">Store 1 VAT Excl</td><td style=";">Store 1 etc1</td><td style=";">Store 1 etc2</td><td style=";">Store 1 etc3</td><td style=";">Store 2 FVF</td><td style=";">Store 2 VAT</td><td style=";">Store 2 VAT Excl</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">6789</td><td style="text-align: right;;">10</td><td style="text-align: right;;">20</td><td style="text-align: right;;">30</td><td style="text-align: right;;">40</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">12345</td><td style="text-align: right;;">110</td><td style="text-align: right;;">120</td><td style="text-align: right;;">130</td><td style="text-align: right;;">140</td><td style="text-align: right;;">150</td><td style="text-align: right;;">150</td><td style="text-align: right;;">150</td><td style="text-align: right;;">150</td><td style="text-align: right;;">150</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Data</p><br /><br />

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!

Last edited:

#### AlanY

##### Well-known Member
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

##### Board Regular
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

##### Board Regular
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

Replies
2
Views
25
Replies
2
Views
83
Replies
3
Views
70
Replies
6
Views
85
Replies
3
Views
94

1,109,426
Messages
5,528,690
Members
409,830
Latest member
KT50

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
• VBA vlookup multiple results
Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
• Extract values for earliest/latest times
I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...