Index/Match Assistance

[Bwiles96]

I've spent the past 5 hours trying to figure out my index match formula, but my brain is mush at this point. Every instance of index/match i've been unable to return my desired value.

I am trying to return the value of the date from the reference sheet based on the "batch ticket" number on the output sheet

Sheet with formula (named Output):

Data is in A3 to C3

The formula I most recently tried in A1 above was =INDEX('Stored Data'!D2:XFD780,MATCH(Output!$A$2, 'Stored Data'!D:D,0),MATCH(Output!C3,'Stored Data'!D2:XFD780,0))


Reference Sheet (named Stored Data)

Data is in D2 to E4

Is the reason I am returning "N/A" because there is multiple instances of the word "Date"? That is the only reason I can think that this is not working.

Send help lol

 

[jambar]

If I get your question right....

The second Match ...,MATCH(Output!C3,'Stored Data'!D2:XFD780,0)....can have only one dimension vector...not D2:XFD780

The formula I most recently tried in A1 above was =INDEX('Stored Data'!D2:XFD780,MATCH(Output!$A$2, 'Stored Data'!D:D,0),MATCH(Output!C3,'Stored Data'!D2:XFD780,0))

 

[jambar]

Try using below formula in output sheet cell A1

=INDEX('Stored Data'!E:,MATCH(Output!$C1, 'Stored Data'!E:E,0)+1)

 

[SunnyAlv]

Try This
provided that the date is exactly 1 cell below the batch no

Copy to Row "A3"

=OFFSET(INDEX('Stored Data'!E:E,MATCH(Output!$C$3,'Stored Data'!E:E,0)),1,0)

 

[jambar]

Try This
provided that the date is exactly 1 cell below the batch no

Copy to Row "A3"

=OFFSET(INDEX('Stored Data'!E:E,MATCH(Output!$C$3,'Stored Data'!E:E,0)),1,0)

Is that $C$3 or C1?

 

[SunnyAlv]

Is that $C$3 or C1?

Reference to Batch No directly on "C3"

 

[Bwiles96]

I will give it all a try tomorrow morning! Thanks for the feedback. Will touch base with y’all when I can

 

[Alex Blakenburg]

Since I can see some issues with both the above, here is another option:

20231213 Index Match Bwiles96.xlsx
	A	B	C
1
2	Date	Product	Batch
3	12/12/2023		123456
4	1/07/2023		888888
Output

Cell Formulas
Range	Formula
A3:A4	A3	=INDEX('Stored Data'!$E$2:$E$780, MATCH(C3,'Stored Data'!$E$2:$E$780,0) + 1, 0)

Sample Values Table in case it helps others.

20231213 Index Match Bwiles96.xlsx
	D	E
1
2	Batch Ticket	123456
3	Date	12/12/2023
4	Package type	Bag
5	Batch Ticket	999999
6	Date	20/02/2023
7	Package type	Carton
8	Batch Ticket	888888
9	Date	1/07/2023
10	Package type	Crate
11	Batch Ticket	987654
12	Date	5/09/2023
13	Package type	Carton
Stored Data

 

[Bwiles96]

Since I can see some issues with both the above, here is another option:

20231213 Index Match Bwiles96.xlsx
	A	B	C
1
2	Date	Product	Batch
3	12/12/2023		123456
4	1/07/2023		888888
Output

Cell Formulas
Range	Formula
A3:A4	A3	=INDEX('Stored Data'!$E$2:$E$780, MATCH(C3,'Stored Data'!$E$2:$E$780,0) + 1, 0)

Sample Values Table in case it helps others.

20231213 Index Match Bwiles96.xlsx
	D	E
1
2	Batch Ticket	123456
3	Date	12/12/2023
4	Package type	Bag
5	Batch Ticket	999999
6	Date	20/02/2023
7	Package type	Carton
8	Batch Ticket	888888
9	Date	1/07/2023
10	Package type	Crate
11	Batch Ticket	987654
12	Date	5/09/2023
13	Package type	Carton
Stored Data

This works like a charm so far. Thank you so much. I was slamming my head into the wall yesterday!


Thank y'all for the assistance as well

SunnyAlv​

jambar​

 

[Bwiles96]

SunnyAlv​

jambar​

Alex Blakenburg​

So I realize I should've been more detailed in my initial question

I have uploaded a smaller sample size of my workbook.

I will have multiple columns with different batch ticket numbers.

I need to be able to look up the date, package type and result of test 1, test 2, test 3, test 4, test 5 for batch ticket 1 and then the date, package type and result of test 1, test 2, test 3, test 4, test 5 for batch ticket 2 and so on and so forth.

Any ideas?

Reference Page:

Output Page with current formula in A2: =INDEX('Stored Data'!$E$2:$E$780, MATCH(C2,'Stored Data'!$E$2:$E$780,0) + 1, 0)

NOTE: Column Product is mislabeled. It should say package type, just to not confuse anyone