formula errors when attempting to use Index, Match, Indirect & Address

stuk71

New Member
Joined
Feb 1, 2021
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
Hi all

I am attempting to reference cell values from another sheet, but to be able to check a certain sheet depending on criteria of cell A1 - but I am getting myself in knots and presumably missing an important part:


cell A1 = "2020" (check 2020 Sales Data)
cell A1 = "2021" (check 2021 Sales Data)

I feel that I have sorted this part ^

Once sheet is referenced, I am looking for a specific row (retailer name) that is displayed in A4

cell A4 = "Tesco"

I feel I have sorted this part too ^

The last part, I would like to display the cell value of a given week number that is displayed in B3

cell B3 = 1 (for week1 on the given sheet)

=IFERROR(MATCH($A4,INDIRECT("'"&$A$1&" Sales Data Table'!$A:$A"),0),MATCH("Week"&$B$3,INDIRECT("'"&$A$1&" Sales Data Table'!$2:$2"),0),"")

this causes an error, but I can't figure out how to sort it. been watching lots of online tutorials but I am still not getting it to work.

Any help/advice will be much appreciated
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
723
IFERROR has two parameters. your formula has three parameters.
=IFERROR(MATCH($A4,INDIRECT("'"&$A$1&" Sales Data Table'!$A:$A"),0),MATCH("Week"&$B$3,INDIRECT("'"&$A$1&" Sales Data Table'!$2:$2"),0),"")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,670
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Do you mean something like
Excel Formula:
=IFERROR(INDEX(INDIRECT("'"&$A$1&" Sales Data Table'!$A$3:$Z$2000"),MATCH($A4,INDIRECT("'"&$A$1&" Sales Data Table'!$A$3:$A$2000"),0),MATCH("Week"&$B$3,INDIRECT("'"&$A$1&" Sales Data Table'!$A$2:$Z$2"),0)),"")
You may need to change the ranges.
 

stuk71

New Member
Joined
Feb 1, 2021
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
Thank you both, although, I am not getting it to work it to work... hmmmmmm
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,670
Office Version
  1. 365
Platform
  1. Windows
In that case I suggest you post some sample data, along with what you are trying to do.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,152
Members
416,224
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top