Index Match or IF Nested Formulas need most efficient Way Formula

daleholden

Board Regular
Joined
Sep 22, 2002
Messages
236
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?
 

Some videos you may like

Excel Facts

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

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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
Joined
Oct 30, 2014
Messages
4,191
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Sep 22, 2002
Messages
236
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
Joined
Oct 30, 2014
Messages
4,191
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Sep 22, 2002
Messages
236
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
Joined
Oct 30, 2014
Messages
4,191
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 30, 2014
Messages
4,191
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Sep 22, 2002
Messages
236
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
Joined
Sep 22, 2002
Messages
236
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
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    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...
Top