Building Summary Report - Find Multiple Matches in a table

Hello,

In a earlier post I attached a file for reference that probably wasn't very helpful.

An updated file has been attached.

What I'm needing help with is populating the return table with dates and values that pertain to two columns in the dataset. You'll see in the criteria box, i'm looking for records that reference a particular type if they pertain to a particular lot. Records that match the criteria, need to be returned to the return table. You'll notice in the Return Table, in the date column, i'm using a formula built with the index function. It's inadequate because it's finding all the lot records that are WM141. I want to limit the findings to Lot WM141 & Type = Purchase in this example.

Thanks in advance for the help.

-Manny

Be aware that for some of us, the day is nearly over.

In I7 copied down:

=IFERROR(SMALL(IF(Table1[Lot]=$J$4,IF(Table1[Type]=$J$3,Table1[Date])),ROW(I7)-6),"")

In J7 copied down:

=IF(I7="","",SUMIFS(Table1[Value],Table1[Lot],$J$4,Table1[Type],$J$3,Table1[Date],L7))

It will depend where said text is. Provide a fresh sample workbook and I'll advise.

Here you go:

=IF(I7="","",LOOKUP(2,1/((Table1[Lot]=$J$4)*(Table1[Type]=$J$3)*(Table1[Date]=I7)),Table1[Description]))

one formula for date value description


I7=IFERROR(INDEX(C$5:C$14,SMALL(IF($B$5:$B$14=$J$3,IF($A$5:$A$14=$J$4,ROW(C$5:C$14)-ROW(C$5)+1)),ROWS($C$5:C5))),"")


Control +shift+enter

copy across and down


colunm I format as date

colunms j K format as General

Please attach the workbook here.

You will also need to be specific about what isn't working for you. I presume you have both workbooks open?

You either have a data mismatch or haven't set it up correctly. Unfortunately, it's past 1700 on Easter Sunday here, so my helping time is up, but hopefully someone Stateside will be able to pick this up.

PS I won't follow external links to workbooks - sorry.

You, too - I'll look in again tomorrow to see if you got sorted.

No, that’s not allowed. You can bump the thread once every 24 hours. I will look tomorrow if you attach the workbook here. As I said before, I don’t follow links to file-sharing sites.

Try this:

=IFERROR(SMALL(IF('C:\Users\Mavila\Dropbox\GWR Group\Manny Avila\GWR Cattle Tracking\GWR Cattle Tracking System.xlsx'!TablePSD[Lot]=$B$3,IF('C:\Users\Mavila\Dropbox\GWR Group\Manny Avila\GWR Cattle Tracking\GWR Cattle Tracking System.xlsx'!TablePSD[Type]=$A$43,'C:\Users\Mavila\Dropbox\GWR Group\Manny Avila\GWR Cattle Tracking\GWR Cattle Tracking System.xlsx'!TablePSD[DATE])),ROW(B45)-44),"")

Can you provide a sample workbook that has everything in the workbook. I can't keep fixing external references in order to help you - it takes me far too long. Sorry!

I would have thought you'd have been able adapt one of the formulae you've already been given for this, though - did you try? The function you've dumped in that cell doesn't exist - there is XLOOKLUP and VLOOKUP, but no XVLOOKUP.

Yes, but you've still left me to fix the broken formulae links! Come on ...

And the table at the bottom has no data in it, so what are you expecting to match in order to fill in that final column???

Sorry - I can't work like this.

Your workbook is still full of #REF! errors, and you have not even bothered to mock up what you want to see in that column. All you have given is an empty wash of yellow. You aren't even meeting me halfway.

Have you tried adapting this yourself yet?

=IFERROR(SMALL(IF('C:\Users\Mavila\Dropbox\GWR Group\Manny Avila\GWR Cattle Tracking\GWR Cattle Tracking System.xlsx'!TablePSD[Lot]=$B$3,IF('C:\Users\Mavila\Dropbox\GWR Group\Manny Avila\GWR Cattle Tracking\GWR Cattle Tracking System.xlsx'!TablePSD[Type]=$A$43,'C:\Users\Mavila\Dropbox\GWR Group\Manny Avila\GWR Cattle Tracking\GWR Cattle Tracking System.xlsx'!TablePSD[DATE])),ROW(B45)-44),"")

You were trying to adapt the wrong formula for this. It is now clear (because you have filled in the details) that you needed to adapt the one in post #9:

=IFNA(LOOKUP(2,1/((TablePSD[Lot]=$B$3)*(TablePSD[Type]=$A$43)*(TablePSD[DATE]=[@Date])*(TablePSD[CMS'#]=[@CMS])),TablePSD[Note]),"")

Like this:

=IF(LOOKUP(2,1/((TablePSD[Lot]=$B$3)*(TablePSD[Type]=$A$43)*(TablePSD[DATE]=[@Date])*(TablePSD[CMS'#]=[@CMS])),TablePSD[Note])=0,"",LOOKUP(2,1/((TablePSD[Lot]=$B$3)*(TablePSD[Type]=$A$43)*(TablePSD[DATE]=[@Date])*(TablePSD[CMS'#]=[@CMS])),TablePSD[Note]))

Are we done yet?

That's correct if you wish to exclude #N/A errors as well (see this used in post #32).

Glad you've got what you need now.