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

[stuk71]

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

 

[Takae]

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]

Hi & welcome to MrExcel.
Do you mean something like

=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]

Thank you both, although, I am not getting it to work it to work... hmmmmmm

 

[Fluff]

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.