Return value based off 2 criteria.

**iamdavid** · 04-10-2010, 04:28 PM

I can't seem to figure out how to return a value from a cell based on 2 criterias. Date(columns) and #'s(Row). I've tried sumif, sumifs. sumproduct...

I've attached a sample to show what I'm needing. I need cell B6 on the "display" sheet to find how many instances occured on 4/1 from set 13.

Many thanks for any help. I've spent many hours reading all the great info on this site. Very helpful..

**contaminated** · 04-10-2010, 04:36 PM

try in Br

=INDEX(data!$B$3:$I$65,MATCH(display!$A3,data!$A$3:$A$65,0),MATCH(display!B$2,data!$B$2:$I$2,0))

or

1 =HLOOKUP(display!B$2,data!$B$2:$I$65,MATCH(display!$A3,data!$A$2:$A$65,0),0)
2 =VLOOKUP($A3,data!$A$2:$I$65,MATCH(display!B$2,data!$A$2:$I$2,0),0)
3 =OFFSET(data!$A$2,MATCH(display!$A3,data!$A$3:$A$65,0),MATCH(display!B$2,data!$B$2:$I$2,0))
4 =INDIRECT(ADDRESS(MATCH(display!$A3,data!$A$3:$A$65,0)+ROW(data!$A$2),MATCH(display!B$2,data!$B$2:$I$2,0)+COLUMN(data!$A$2),,,"data"))
copy down then across

**DonkeyOte** · 04-11-2010, 03:18 AM

If DATA is an actual Pivot Table (rather then copied values) you could also use the GETPIVOTDATA function.

If the above is the case then you can encase the GETPIVOTDATA call within an IFERROR function to account for possibility of set / date combination not appearing with the PT Data Table.

GETPIVOTDATA function has the advantage that it will adjust all references as and when the Pivot itself refreshes (ie no ranges will need adjusting based on it's location, size etc...)

**ghiorghiu** · 04-11-2010, 03:58 AM

Please help me to find a formula able to return in a specific cell the 2 last numbers of a bar code only if it starts with a specified string.
Thank you in advance!

**iamdavid** · 04-11-2010, 03:51 PM

Originally Posted by DonkeyOte

If DATA is an actual Pivot Table (rather then copied values) you could also use the GETPIVOTDATA function.

If the above is the case then you can encase the GETPIVOTDATA call within an IFERROR function to account for possibility of set / date combination not appearing with the PT Data Table.

GETPIVOTDATA function has the advantage that it will adjust all references as and when the Pivot itself refreshes (ie no ranges will need adjusting based on it's location, size etc...)

Thanks DonkeyOte! The GETPIVOTDATA is a wonderful tool for this. One more question though.. The real page I'm using this pulls from a page with over 100 days and over 100 set(rows) within the pivot table. When I copy the formula from one cell to the next it keeps it exactly the same instead of changing the date to the next. Any way I can have it do this instead of changing each cell to the corresponding date?

Thanks again!..

BTW: I know this a probably a strange need for someone but it's the only option I can find that will allow me to display the data I'm trying to compile. The main sheet will actually be pulling info off multiple pivot tables being generated out of 3 different databases. I've set it all up except for this final summary sheet. Thanks!!

**DonkeyOte** · 04-11-2010, 04:32 PM

Yes, you can use "variables" for the various PT "item" criteria, eg:

B2: =IFERROR(GETPIVOTDATA(fieldofinterest,PTlocation,fieldname1,$A2,fieldname1,B$1),0)

as you copy B2 across the matrix so references A2 and B1 will adjust accordingly

**iamdavid** · 04-11-2010, 05:37 PM

Originally Posted by DonkeyOte

Yes, you can use "variables" for the various PT "item" criteria, eg:

B2: =IFERROR(GETPIVOTDATA(fieldofinterest,PTlocation,fieldname1,$A2,fieldname1,B$1),0)

as you copy B2 across the matrix so references A2 and B1 will adjust accordingly

Thanks for the quick responses and excellent help. It works great!!!