Multi-column Indexing [SOLVED]

TrackerTest01.xlsx

After doing some searching on this form I was unable to find an answer that would help.

For my first post I would ask for some help. I have been asked to provide my company with a work book that tracks the inventory in several of our focus stores. Attached is a small workbook identifying what I am trying to do.

I am trying to match 3 or 4 columns and then provide me with an indexed result.

for example I am trying to retrieve an indexed number for Cell B5. I need it to correlate with cells B4,A5, and B2. If I could get some help I would greatly appreciate it.

I read somewhere that by combining the 3 columns and creating a specific number somehow and then indexing that; that might work.

Again thank you in advanced I look forward to spending some time here.

plague5050,

Welcome to the forum!
Can you provide the expected results? The table you're looking to populate has all twelve months, but the Store sheets only have data for April and May. The table on 'Tracker' also shows "Current Month Purchases", so seeing that the current month is April, should it only look at April's data? The 'Tracker' table also uses different terminology than the data on the Store sheets. This should be corrected so that all terminology is uniform. It makes it easier to comprehend for anybody looking at the data, and also makes it easier for Excel to work with the data properly.

plague5050,

Attached is a modified version of your posted workbook.
In sheet 'Tracker' cell B5 and copied over and down to cell M7 is this formula:

=IF(OR($B$1="",$B$2=""),"",IFERROR(INDEX(INDIRECT("'"&$B$1&"'!D2:D200"),MATCH(1,INDEX((LEFT(INDIRECT("'"&$B$1&"'!A2:A200"),3)=B$4)*(INDIRECT("'"&$B$1&"'!B2:B200")=$B$2)*(INDIRECT("'"&$B$1&"'!C2:C200")=IF($A5=$A$5,"Curr Yr",IF($A5=$A$6,"Prev Yr","TY SC Avg"))),),0)),""))

Note that the Indirect function works because the Stores you can select are identical to the sheet names (you can select Store1 or Store2 and you have sheets with identical names 'Store1' and 'Store2').