INDEX, MATCH WITH "&"

[wonderfulsomebody123]

=INDEX(A:A,MATCH("9-"&U29,F:F))

I want the above formula to return a value from column "A" by matching "9-number" from F column.

For example, U29 cell = 135. So 9-135 would be in F:F column and it would give me the data in A column.

Please help. Thanks!

 

[Fluff]

In what way doesn't that work?

 

[etaf]

so what happens
can you provide a sample

that should work
formula in H2

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2								fred
3	fred					9-135
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29																					135
Sheet1

Cell Formulas
Range	Formula
H2	H2	=INDEX(A:A,MATCH("9-"&U29,F:F))

 

[wonderfulsomebody123]

In what way doesn't that work?

For some reason it brings up the the data from column A but the data is wrong.

 

[etaf]

we may need to see an example

as it does work

can 9-135 appear more than once

as you can see on my xl2bb it works returning fred

 

[Fluff]

Try adding the final argument to the match function like

=INDEX(A:A,MATCH("9-"&U29,F:F,0))

 

[wonderfulsomebody123]

TEST.xlsx
	A	B	C	D	E	F	G	H
1	April 16, 2018	83	83	8	8-1		1	April 21, 2018
2	April 17, 2018	72	72	7	7-1		1	April 18, 2018
3	April 18, 2018	13	13	1	1-1		1	April 26, 2018
4	April 19, 2018	59	59	5	5-1		1	April 27, 2018
5	April 20, 2018	42	42	4	4-1		1	April 20, 2018
6	April 21, 2018	1	01	0	0-1		2	April 23, 2018
7	April 22, 2018	62	62	6	6-1		1	April 22, 2018
8	April 23, 2018	52	52	5	5-2		1	April 17, 2018
9	April 24, 2018	91	91	9	9-1		2	April 25, 2018
10	April 25, 2018	84	84	8	8-2		3	April 29, 2018
11	April 26, 2018	25	25	2	2-1
12	April 27, 2018	37	37	3	3-1
13	April 28, 2018	99	99	9	9-2
14	April 29, 2018	91	91	9	9-3			April 27, 2018
Sheet1

Cell Formulas
Range	Formula
C1:C14	C1	=IF(LEN(B1)=1,"0","")&B1
D1:D14	D1	=LEFT(C:C,1)
E1:E14	E1	=D1&"-"&COUNTIF($D$1:D1,D1)
G1	G1	=SUMPRODUCT(--(LEFT(C:C)="0"))
H1	H1	=LOOKUP(2,1/("0"=LEFT(C:C,1)),A:A)
G2	G2	=SUMPRODUCT(--(LEFT(C:C)="1"))
H2	H2	=LOOKUP(2,1/("1"=LEFT(C:C,1)),A:A)
G3	G3	=SUMPRODUCT(--(LEFT(C:C)="2"))
H3	H3	=LOOKUP(2,1/("2"=LEFT(C:C,1)),A:A)
G4	G4	=SUMPRODUCT(--(LEFT(C:C)="3"))
H4	H4	=LOOKUP(2,1/("3"=LEFT(C:C,1)),A:A)
G5	G5	=SUMPRODUCT(--(LEFT(C:C)="4"))
H5	H5	=LOOKUP(2,1/("4"=LEFT(C:C,1)),A:A)
G6	G6	=SUMPRODUCT(--(LEFT(C:C)="5"))
H6	H6	=LOOKUP(2,1/("5"=LEFT(C:C,1)),A:A)
G7	G7	=SUMPRODUCT(--(LEFT(C:C)="6"))
H7	H7	=LOOKUP(2,1/("6"=LEFT(C:C,1)),A:A)
G8	G8	=SUMPRODUCT(--(LEFT(C:C)="7"))
H8	H8	=LOOKUP(2,1/("7"=LEFT(C:C,1)),A:A)
G9	G9	=SUMPRODUCT(--(LEFT(C:C)="8"))
H9	H9	=LOOKUP(2,1/("8"=LEFT(C:C,1)),A:A)
G10	G10	=SUMPRODUCT(--(LEFT(C:C)="9"))
H10	H10	=LOOKUP(2,1/("9"=LEFT(C:C,1)),A:A)
H14	H14	=INDEX(A:A,MATCH("9-"&G1,E:E))
Press CTRL+SHIFT+ENTER to enter array formulas.

As you may see, "9-1" should return April 24, 2018, not April 27, 2018 as shown in cell H14.

 

[Fluff]

See post#6.

 

[wonderfulsomebody123]

@etaf ... 9-"number" don't repeat

@Fluff Adding the "0" to the final argument doesn't help either

 

[wonderfulsomebody123]

@Fluff Strange, in the small, test document I created, the "0" at the end works. However, the same formula with the "0" added at the end in the full spreadsheet causes the result to show "N/A". Could it be that there is too much data to process?