#Value error for hlookup, match and Index combine formula

[Pi*]

Good evening,

Sorry I am re posting since I received an error the first time round.

I have an excel array formula that looks up the sq ft of a number of stores on a different sheet(Sq ft Pull) based on the row number in the same workbook by using hlookup which works fine:
=(HLOOKUP(D$5,'Sq ft Pull'!$C$8:$DP$439,$C59+$C$2,False)....but now I am trying to combine the formula with Match and Index function so that my results will produce the correct number corresponding to the right period when the period (months) in cell C3 changes....so I came up with the formula below which I gives me a #Value error an I can't figure out why;
=HLOOKUP(D$5,'Sq ft Pull'!$C$8:$DP$439,$C59+$C$2,INDEX('Sq ft Pull'!C8:DP439,MATCH(C3,'Sq ft Pull'!C8:C439,0),1)).

I was hoping you could advise and help me correct the array formula since I presume I am doing something wrong. I have attached a sample file of my error.

Best Regards,

Pi*

[FDibbins]

This should work for you...
=IFERROR(INDEX('Sq ft Pull'!$E$4:$G$11,MATCH(Sheet1!$C$3&Sheet1!$B5&"Plan",INDEX('Sq ft Pull'!$B$4:$B$11&'Sq ft Pull'!$A$4:$A$11&'Sq ft Pull'!$D$4:$D$11,0),0),MATCH(D$4,'Sq ft Pull'!$E$3:$G$3,0)),"")

Change the bolded part for Adj Act and Act where needed

Also, I know that is dummy info, but I changed the values in sheet2 A5:A6 to all read the same, so the formula could be tested

[Pi*]

Thank you Ford I appreciate your time and response. I was hoping when the formula is dragged across in Sheet 1 it will populate the correct Apparel, Socks and Vehicular numbers for corresponding periods ie Plan, Adj Actuals and Actuals on the Sq ft pulll sheet and also when the period (month) changes in cell C3 (in Sheet 1) to Mar the formula results will shows Mar numbers for Plan, Adj Actuals and Actuals which is why I initially started with an hlookup formula but it didn't work.
Any help would be most appreciated.

Thank you again.

[lexusap]

Hi,

i have use different approach of SUMPRODUCT.

see attached file.

It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.


Cheers
Albert

[FDibbins]

I was hoping when the formula is dragged across in Sheet 1 it will populate the correct Apparel, Socks and Vehicular.....

That is exactly what it does. If you have applied it correctly, and you actually have values that match (your sample has 314 on sheet 1 but nothing to match that on sheet 2?), then it will pull out matching data...

	B	C	D	E	F	G	H	I	J	K	L	M	N
3		Feb	1	Plan			2	Adj Actuals			3	Actuals
4			Apparel	Socks	Vehicular		Apparel	Socks	Vehicular		Apparel	Socks	Vehicular
5	315	Sq ft	500	36	844
6	316						265	100	966
7	317										2	5	99

D5=IFERROR(INDEX('Sq ft Pull'!$E$4:$G$11,MATCH(Sheet1!$C$3&Sheet1!$B5&"Plan",INDEX('Sq ft Pull'!$B$4:$B$11&'Sq ft Pull'!$A$4:$A$11&'Sq ft Pull'!$D$4:$D$11,0),0),MATCH(D$4,'Sq ft Pull'!$E$3:$G$3,0)),"")
Copy this across 2, then repeat for Adj Act and Act, and change the bolded part accordingly

Then copy the whole line down as needed

[Pi*]

I admire the help from both of you. I tried to upload the sample file again with the desired results but I was unable to. All the same is there any way the results of the formula can be on the same row as below;

Feb 1 Plan 2 Adj Actuals 3 Actuals
Apparel Socks Vehicular Apparel Socks Vehicular Apparel Socks Vehicular
314 Sq ft $500.00 $36.00 $844.00 $265.00 $100.00 $966.00 $2.00 $5.00 $99.00 << <<Desired Results trying to achieve
and when Month is selected in cell C3 the values change accordingly corresponding to Month values on Sq ft Pull tab




so that when cell C3 which will be a data validation cell changes to a different month the results changes accordingly. I inserted numbers 1,2 and 3 next to Plan, Adj Actuals and Actuals so that somehow the formula will add these to the row #314 (ie 315,316 and 317) so the the results can be displayed on the same row rather than different rows as you show. I apologize for being a pain but I hope I am making sense.

Hopefully you can assist.

Regards,

Pi

[FDibbins]

314 Sq ft $500.00 $36.00 $844.00 $265.00 $100.00 $966.00 $2.00 $5.00 $99.00 << <<Desired Results trying to achieve

Yes, that is what it will do - PROVIDED that the other rows on sheet 2 also contain 314 - which they dont in your sample.

If you look at the attached file, I have added an extra set of data on sheet 2 and made all 3 rows 314, and then entered 314 in B5 on sheet 1. As you can see, it pulls out the info exactly as you have asked for

[Pi*]

Hi Ford,

I can't thank you enough. You are a true master of your craft. Your formula did exactly what I asked before.

Thank you very much.

[Pi*]

Good afternoon Albert,

Your assistance with my request was invaluable.

Thank you Sir.

[FDibbins]

Im happy we got this resolved for you, thanks for the feedback, all