#NUM when looking for second result using MATCH INDEX

[Janice Z]

I am trying to summarize available inventory for certain projects in a new document. I have used SUMIFS to total the number available using three criteria: Species (Column D in my inventory document and column A in my summary document), Zone (Column A in my inventory document and column C in my summary document), and Project (Column AH in my inventory document and Column D in my summary document).

=SUMIFS('[SI 15 10 26.xlsx]Current Inventory'!AG:AG,'[SI 15 10 26.xlsx]Current Inventory'!D:D,A10,'[SI 15 10 26.xlsx]Current Inventory'!AH:AH,D10,'[SI 15 10 26.xlsx]Current Inventory'!A:A,C10)

The above formula worked.

Now I need the all of the lot-codes listed in the next cells. (ideally all of the lot codes in one cell, but otherwise I could do multiple columns to the right).

I have the following formula which is working to give me the first match:

{=INDEX('[SI 15 10 26.xlsx]Current Inventory'!$F$2:$F$1024,MATCH(1,(C10='[SI 15 10 26.xlsx]Current Inventory'!$A$2:$A$953)*(A10='[SI 15 10 26.xlsx]Current Inventory'!$D$2:$D$953)*(D10='[SI 15 10 26.xlsx]Current Inventory'!$AH$2:$AH$1105),0))}

I am attempting to use the following formula to give me the rest of the matches:

{=INDEX('[SI 15 10 26.xlsx]Current Inventory'!$F$2:$F$1024,SMALL(MATCH(1,((C57='[SI 15 10 26.xlsx]Current Inventory'!$A$2:$A$953)*(A57='[SI 15 10 26.xlsx]Current Inventory'!$D$2:$D$953)*(D57='[SI 15 10 26.xlsx]Current Inventory'!$AH$2:$AH$1105)),0),1))}

When I leave the red 1 as a 1 it returns the same lotcode as the first index formula, but when I change it to a 2 it gives #NUM.

Also, once I have the second formula worked out, would it be possible to have all the lot codes show up in one cell using &? As in:

{=INDEX('[SI 15 10 26.xlsx]Current Inventory'!$F$2:$F$1024,MATCH(1,(C10='[SI 15 10 26.xlsx]Current Inventory'!$A$2:$A$953)*(A10='[SI 15 10 26.xlsx]Current Inventory'!$D$2:$D$953)*(D10='[SI 15 10 26.xlsx]Current Inventory'!$AH$2:$AH$1105),0))&", "&=INDEX('[SI 15 10 26.xlsx]Current Inventory'!$F$2:$F$1024,SMALL(MATCH(1,((C57='[SI 15 10 26.xlsx]Current Inventory'!$A$2:$A$953)*(A57='[SI 15 10 26.xlsx]Current Inventory'!$D$2:$D$953)*(D57='[SI 15 10 26.xlsx]Current Inventory'!$AH$2:$AH$1105)),0),1))}

Thanks in advance!

 

[DILIPandey]

{=INDEX('[SI 15 10 26.xlsx]Current Inventory'!$F$2:$F$1024,SMALL(MATCH(1,((C57='[SI 15 10 26.xlsx]Current Inventory'!$A$2:$A$953)*(A57='[SI 15 10 26.xlsx]Current Inventory'!$D$2:$D$953)*(D57='[SI 15 10 26.xlsx]Current Inventory'!$AH$2:$AH$1105)),0),1))}

Can you give a sample data for this ?

Regards,
DILIPandey

 

[Janice Z]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Zone[/TD]
[TD]Species[/TD]
[TD]LOT CODE[/TD]
[TD]Approx #[/TD]
[TD]Project [/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD]Betula alleghaniensis[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]27899914AAY01[/TD]
[TD]594
[/TD]
[TD]GH[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD]Betula alleghaniensis[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]29891113OSP01[/TD]
[TD]2524[/TD]
[TD]SUD[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD]Betula alleghaniensis[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]34150915SDW01[/TD]
[TD]7865[/TD]
[TD]GH[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD]Betula alleghaniensis[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]34111107SWN01[/TD]
[TD]3421[/TD]
[TD]GH[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD]Betula papyrifera[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]34150917ADA01[/TD]
[TD]486412[/TD]
[TD]SUD[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD]Betula papyrifera[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]34151113OSP01[/TD]
[TD]48525[/TD]
[TD]SUD[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD]Betula papyrifera[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]37150908SWN01[/TD]
[TD]55[/TD]
[TD]GH[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD]Betula papyrifera[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]37150908SWN19[/TD]
[TD]78965[/TD]
[TD]GH[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD]Betula papyrifera[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]37100901MEG05[/TD]
[TD]227[/TD]
[TD]SUD[/TD]
[/TR]
</tbody>[/TABLE]

 

[Janice Z]

i couldnt firgure out how to put up a picture of my actual data, but this is a sample of the important colunmns

 

[DILIPandey]

okay.... based on this sample data (let's assume they are in columns A to E, what you will look for using Match Index ?


Regards
DILIPandey

 

[Janice Z]

If the zone, species, and project match, I would like it to spit out the second possible lotcode.

For example, if i put the formula into E2 in the following table, it would search (in the first table) column A for Zone 29, Column B for Betula alleghaniensis, and column E for GH. But since there is only 1 lotcode that fits all three criteria (D2), it comes back as a #N/A error. However if I put the formula in E3 in the following table, it searches the original table for Z34, Betula alleghaniensis, and GH. There are 2 lotcodes that fit all three criteria (34150915SDW01 and 34111107SWN01). so it returns 34111107SWN01 since it is the second lotcode listed in the table.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Zone[/TD]
[TD]Species[/TD]
[TD]Project[/TD]
[TD]Lotcode[/TD]
[TD]second lotcode[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]Betula alleghaniensis[/TD]
[TD]GH[/TD]
[TD]27899914AAY01[/TD]
[TD] #N/A[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]Betula alleghaniensis[/TD]
[TD]GH[/TD]
[TD]34150915SDW01[/TD]
[TD]34111107SWN01[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD][TABLE="class: cms_table, width: 141"]
<tbody>[TR]
[TD]Betula papyrifera[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]GH[/TD]
[TD]37150908SWN01[/TD]
[TD]37150908SWN19[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD][TABLE="class: cms_table, width: 141"]
<tbody>[TR]
[TD]Betula papyrifera[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]SUD[/TD]
[TD]37100901MEG05[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[DILIPandey]

Still not clear to me.. better if you mention your expected results based on table which is there in post #3.



Regards,
DILIPandey