Need to Index/Match based on Two Criteria in same row

[mhs321]

Hello, Excel wizards, I hope you are doing well.

I am trying to use Index/Match To locate the Nth instance of specific text.
The catch:
Nth instance can change
Both Criteria are in Col A

What I want to locate: Service ID for IDS where the Access number is 111111111 (Purple cell, Col A, in the pic)

Match to find *111111111* - check, Locates first instance (A6)
Match to find IDS - check, Locates first instance (A2)

I need to figure out how to tell Excel to find at each instance of the Access Number and look at the Row above it.
If/When Excel finds a cell that contains "IDS" then return the text in that cell.

K11 = Access Number
Access Number Minus 1 ROW = INDEX($A:$A,MATCH("*"&K11&"*",$A:$A,0)-1,1)

Final Code Format:
=INDEX(A:A,MATCH(1,Two Criteria,0),1)

Two Criteria = (A:A=Access Number Minus 1 ROW) * (A:A="IDS*")
(A:A=INDEX($A:$A,MATCH("*"&K11&"*",$A:$A,0)-1,1)) * (A:A="IDS*")

Closest I have to final code is:
=INDEX(A:A,MATCH(1,(A:A=INDEX($A:$A,MATCH("*"&K11&"*",$A:$A,0)-1,1)) * (A:A="IDS*"),0),1)

This isn't right because (Access Num - 1) = A5 & IDS* = A2

Stuck on how to get the two to triangulate properly and move down to each next instance of the Access Number.

Thanks.

 

[jasonb75]

I think you want something like

=INDEX(A:A,match("IDS*",$A$1:INDEX(A:A,MATCH("*"&K11&"*",$A:$A,0)-1),0))

It is better if you post examples using XL2BB (button on reply box toolbar), screen captures can not be used for testing formulas without retyping your data into excel first, if you use XL2BB we can copy and paste your data and formulas straight into a blank sheet.

 

[mhs321]

Thank you for the tip about XL2BB. I had no idea. Great feature.

I tried the code you provided and it produced #N/A
Evaluating the formula it read
INDEX($A:$A, MATCH("IDS*",$A$1,0))
INDEX($A:$A, #N/A)

I have attached my XL2BB for further review.

Went for a drive. Now I'm thinking I need to use an IF/THEN statement to move it to the next instance.

Invoice Code - MHS.xlsx
	A	B	C	D	E	F	G	H	I	J
1					Circuit	Related Access	Circuit Type	Service ID	Contract/SOF Number	Access Circuit ID
2	IDS				C1234567	111111111	Private IP (PIP)	616161616	212121	E5555555
3					C0999999	777777777	Private IP (PIP)	313131313	212121	E4444444
4					111111111	E5555555
5	Description	Charge Period
6	Access - Service ID: 111111111 Contract/SOF Number: D01234-00						Match
7	Circuit ID: E5555555				IDS	IDS	2
8	Local Access - Op/App Performance:
9	Platinum / Platinum - 10 Mbps - UNI Speed:
10	1 Gbps (GigE)
11	Total 111111111 Access				111111111	Access - Service ID: 111111111 Contract/SOF Number: D01234-00
12						2
13	Access - Service ID: 777777777 Contract/SOF Number: D01234-00					#N/A
14	Circuit ID: E4444444
15	Local Access - Op/App Performance:
16	Platinum / Platinum - 10 Mbps - UNI Speed:					#N/A
17	1 Gbps (GigE)
18	Total 777777777 Access
19
20	IDS - Service ID: 979797979 Contract/SOF Number: 212121
21	Related Service: Access 111111111
22	Internet Dedicated Port - Tiered - 3 Mbps
23	Total 979797979 IDS
24
25	IDS - Service ID: 987654321 Contract/SOF Number: 212121
26	Related Service: Access 777777777
27	Internet Dedicated Port - Tiered - 3 Mbps
28	Total 987654321 IDS
29
30	Private IP (PIP) - Service ID: 616161616 Contract/SOF Number: 212121
31	Circuit ID: C1234567
32	Related Service: Access 111111111
33	PIP Port - 2 Mbps - United States
34	Total 616161616 Private IP (PIP)
35
36	Private IP (PIP) - Service ID: 313131313 Contract/SOF Number: 212121
37	Circuit ID: C0999999
38	Related Service: Access 777777777
39	PIP Port - 2 Mbps - United States
40	Total 313131313 Private IP (PIP)
41	Total 2537518C
42
43
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=LEFT(INDEX($A:$A,MATCH("*"&E2&"*",$A:$A,0)-1,1), FIND("-",(INDEX($A:$A,MATCH("*"&E2&"*",$A:$A,0)-1,1)),1)-2)
H2:H3	H2	=MID(INDEX($A:$A,MATCH("*"&E2&"*",$A:$A,0)-1,1), FIND("Service ID:",INDEX($A:$A,MATCH("*"&E2&"*",$A:$A,0)-1,1),1)+LEN("Service ID: "), (FIND("Contract/SOF",INDEX($A:$A,MATCH("*"&E2&"*",$A:$A,0)-1,1),1)-3)-(FIND("Service ID:",INDEX($A:$A,MATCH("*"&E2&"*",$A:$A,0)-1,1),1)+LEN("Service ID: ")-1))
I2:I3	I2	=RIGHT(INDEX($A:$A,MATCH("*"&E2&"*",$A:$A,0)-1,1), LEN(INDEX($A:$A,MATCH("*"&E2&"*",$A:$A,0)-1,1)) - (FIND("Contract/SOF",INDEX($A:$A,MATCH("*"&E2&"*",$A:$A,0)-1,1),1)+LEN("Contract/SOF Number: ")-1))
J2:J3	J2	=RIGHT(INDEX(A:A,MATCH("*"&F2&"*",A:A,0)+1,1),8)
F2	F2	=RIGHT(INDEX($A:$A,MATCH("*"&E2&"*",$A:$A,0)+1,1),9)
F3:F4	F3	=RIGHT(INDEX(A:A,MATCH("*"&E3&"*",A:A,0)+1,1),9)
F7	F7	=INDEX(A:A,MATCH("*"&E7&"*",A:A,0),1,1)
G7	G7	=MATCH("*" & "IDS" & "*",A:A,0)
F11	F11	=INDEX($A:$A,MATCH("*"&E11&"*",$A:$A,0),1,1)
F12	F12	=MATCH("*" & "IDS" & "*",A:A,0)
F13	F13	=INDEX(A:A,MATCH(1,(A:A=INDEX($A:$A,MATCH("*"&K11&"*",$A:$A,0)-1,1)) * (A:A="IDS*"),0),1)
F16	F16	=INDEX(A:A,MATCH("IDS*",$A$1:INDEX(A:A,MATCH("*"&K11&"*",$A:$A,0)-1),0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:A41	Cell Value	contains "223227766"	text	NO
A3:A41	Cell Value	contains "223227758"	text	NO
A3:A41	Cell Value	contains "C0141065"	text	NO
A3:A41	Cell Value	contains "223227758"	text	NO

 

[jasonb75]

I followed the references from the formulas in post 1, but looking at the above, I think that you just need to change K11 to E11 to make it work

=INDEX(A:A,match("IDS*",$A$1:INDEX(A:A,MATCH("*"&E11&"*",$A:$A,0)-1),0))

Unless I'm having a serious case of brainfart.

 

[mhs321]

That did not work. The address correction was needed but the function is flawed.
The INDEX for the Access Number finds the first instance and returns the Row above = "Description"
The MATCH for *IDS* finds the first instance and returns the cell address = 2
"Description" * 2 = #VALUE!

Evaluation is below.

So I went back and thought about what I am needing Excel to do:
MATCH(Access#)
Look at the Row above for "IDS*"
True = Return value of Row above
False = Move to next instance of Access#
Repeat Until True
or
Return NULL if never True


What I need is a "Do Until Loop"
I haven't written VBA in Excel.
I have in Access but not Excel. I realize it's the same thing but I'm a bit rusty.
Time to knock off the cobwebs...Youtube is my friend.

As always, open to any VBA code suggestions

Thanks.

Here is the Evaluation:
INDEX(A:A,MATCH(1,INDEX((INDEX($A:$A,MATCH("*"&E11&"*",$A:$A,0)-1,1)) * (MATCH("*" & "IDS" & "*",A:A,0)),0,1),0))
INDEX($A:$A,MATCH(1,INDEX((INDEX($A:$A,MATCH("*"&111111111&"*",$A:$A,0)-1,1)) * (MATCH("*" & "IDS" & "*",A:A,0)),0,1),0))
INDEX($A:$A,MATCH(1,INDEX((INDEX($A:$A,MATCH("*111111111*",$A:$A,0)-1,1)) * (MATCH("*" & "IDS" & "*",A:A,0)),0,1),0))
INDEX($A:$A,MATCH(1,INDEX((INDEX($A:$A,6-1,1)) * (MATCH("*" & "IDS" & "*",A:A,0)),0,1),0))
INDEX($A:$A,MATCH(1,INDEX((INDEX($A:$A,5,1)) * (MATCH("*" & "IDS" & "*",A:A,0)),0,1),0))
INDEX($A:$A,MATCH(1,INDEX(("Description") * (MATCH("*" & "IDS" & "*",A:A,0)),0,1),0))
INDEX($A:$A,MATCH(1,INDEX(("Description") * (MATCH("*IDS*",A:A,0)),0,1),0))
INDEX($A:$A,MATCH(1,INDEX(("Description") * (2),0,1),0))
INDEX($A:$A,MATCH(1,INDEX(("Description" * 2),0,1),0))
INDEX($A:$A,MATCH(1,INDEX((#VALUE!,0,1),0))

 

[jasonb75]

Sorry, it wasn't the function that was flawed, it was definitely me having a full scale nuclear brainfart.

I think that I can see what you need now, going to put something together now just to make sure that I'm on the right track, then we can refine it a bit if needed.

 

[jasonb75]

See if this formula does what you need,

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$5:$A$40)/ISNUMBER(SEARCH($E$11,$A$6:$A$41))/ISNUMBER(SEARCH("IDS",$A$5:$A$40)),1)),"No Match")

If you modify the formula for another sheet, note that the INDEX range refers to the entire column, the ROW and second SEARCH ranges refer to the actual data range excluding the last row, the first SEARCH range refers to the actual data range excluding the first row. This is probably the easiest way to check for pairs of rows meeting criteria, I suspect that looping in vba would be considerably slower.

 

[mhs321]

Ooooo!! Functions I have never used.
Thank you, that worked, btw.

I researched AGGREGATE and I have dissected this formula and this is quite brilliant.
Thank you for showing me a new trick!!