#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]

Zone	Species	LOT CODE	Approx #	Project
27	Betula alleghaniensis <tbody> </tbody>	27899914AAY01	594	GH
29	Betula alleghaniensis <tbody> </tbody>	29891113OSP01	2524	SUD
34	Betula alleghaniensis <tbody> </tbody>	34150915SDW01	7865	GH
34	Betula alleghaniensis <tbody> </tbody>	34111107SWN01	3421	GH
34	Betula papyrifera <tbody> </tbody>	34150917ADA01	486412	SUD
34	Betula papyrifera <tbody> </tbody>	34151113OSP01	48525	SUD
37	Betula papyrifera <tbody> </tbody>	37150908SWN01	55	GH
37	Betula papyrifera <tbody> </tbody>	37150908SWN19	78965	GH
37	Betula papyrifera <tbody> </tbody>	37100901MEG05	227	SUD

<tbody>
</tbody>

 

[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.

Zone	Species	Project	Lotcode	second lotcode
27	Betula alleghaniensis	GH	27899914AAY01	#N/A
34	Betula alleghaniensis	GH	34150915SDW01	34111107SWN01
37	Betula papyrifera <tbody> </tbody>	GH	37150908SWN01	37150908SWN19
37	Betula papyrifera <tbody> </tbody>	SUD	37100901MEG05	#N/A

<tbody>
</tbody>

 

[DILIPandey]

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



Regards,
DILIPandey