index match, lookup formula

[Silo]

Hello everyone

I’m struggling with a index match, lookup formula, here’s what I ‘m trying to do

I have a table range $F$5:$EO$36

Column F6:F36 represents dates
From G5:EO5 is the header row with location names
Range G6:EO36 are sum totals

In Cell AI1 I’m trying to right a lookup formula that will match the date reference in $A$3 to the matching date in range $F$5:$EO$36 with the location name in $B$1 that matches G5:EO5 and provide the sum totals G6:EO36

Any help will be greatly appreciated

Thanks everyone

 

[Silo]

This is the formula that was working before I had to modify the pivot table

=IF(ISNA(VLOOKUP(A3,'803 & DAPC Pivot'!$F$5:$EO$36,MATCH(B1,'803 & DAPC Pivot'!$F$5:$EO$5,0),0)),0,VLOOKUP(A3,'803 & DAPC Pivot'!$F$5:$EO$36,MATCH(B1,'803 & DAPC Pivot'!$F$5:$EO$5,0),0))

Thanks for all the help

 

[_PhilW_]

try...

=IF(ISNA(VLOOKUP(TEXT($A$3,"m/d/yyyy")&" Total",'803 & DAPC Pivot'!$F$5:$EO$36,MATCH(B1,'803 & DAPC Pivot'!$F$5:$EO$5,0),0)),0,VLOOKUP(TEXT($A$3,"m/d/yyyy")&" Total",'803 & DAPC Pivot'!$F$5:$EO$36,MATCH(B1,'803 & DAPC Pivot'!$F$5:$EO$5,0),0))

 

[Silo]

The ranges are now different since I had to change the pivot Table

Also the work sheet where the pivot table is on is now labled "DAPC Pivot" and not '803 & DAPC Pivot'

 

[Silo]

I have edited your last suggestion with the new range

=IF(ISNA(VLOOKUP(TEXT($A$3,"m/d/yyyy")&" Total",'DAPC Pivot'!$E$5:$E$300,MATCH(B1,'DAPC Pivot'!$E$4:$S$4,0),0)),0,VLOOKUP(TEXT($A$3,"m/d/yyyy")&" Total",'DAPC Pivot'!$E$5:$E$300,MATCH(B1,'DAPC Pivot'!$E$4:$S$4,0),0))

Now I get #REF

The Pivot Table range $E$4:$S(Depending on how much data range will keep going)

E4:S4 is the header row with Date in Column E, Product Description in Column F & location names Columns G:S

Column E has dates with Date Sum for each day (example: 8/1/2010 Total)
Range G5:S(Depending on how much data range will keep going) are sum totals

In Cell AI1 I’m trying to get the totals by location in names of location are in Range G4:S4 that match the location referenced in B1.

Thanks for the Help

 

[Jonmo1]

You don't need to test the vlookup for ISNA, you only need to test the MATCH..that will shorten and improve performance of the formula...

Also, The vlookup range needs to be the same # of columns as the match range...

Try

=IF(ISNA(MATCH(B1,'DAPC Pivot'!$E$4:$S$4,0)),0,VLOOKUP(TEXT($A$3,"m/d/yyyy")&" Total",'DAPC Pivot'!$E$5:$S$300,MATCH(B1,'DAPC Pivot'!$E$4:$S$4,0),0))

 

[Silo]

PREFECT! I love this Web Site!

You guys ROCK!

Thanks

 

[Jonmo1]

Oh, I stand corrected....There are actually 2 matches going on...1 in the vlookup and 1 in the actual match...

So you do need to test the whole vlookup formula for isna...

So it should be

=IF(ISNA(VLOOKUP(TEXT($A$3,"m/d/yyyy")&" Total",'DAPC Pivot'!$E$5:$S$300,MATCH(B1,'DAPC Pivot'!$E$4:$S$4,0),0)),0,VLOOKUP(TEXT($A$3,"m/d/yyyy")&" Total",'DAPC Pivot'!$E$5:$S$300,MATCH(B1,'DAPC Pivot'!$E$4:$S$4,0),0))

 

[_PhilW_]

I was going to say about the date match in the vlookup, but tbh i'm so confused by this point!

It's worth mentioning if you have Excel 2007 you can use the IFERROR function which is a lot more succinct, because you don't need to repeat the vlookup.

=IFERROR(formula in here,what you want to return if an error occurs)

so...

=IFERROR(VLOOKUP(TEXT($A$3,"m/d/yyyy")&" Total",'DAPC Pivot'!$E$5:$S$300,MATCH(B1,'DAPC Pivot'!$E$4:$S$4,0),0),0)

(i think !)