Find an item by searching bottom up

[Natit]

Hi,
I'm looking for the formula that will get me the Group (cell H2), when I enter the Cat. num (cell F2)
Thx
Nati

שאלה לתפוז.xlsx
	A	B	C	D	E	F	G	H
1	Group	Item	Cat. Num			Cat. Num	Item	Group
2	aa90					44	a7	??
3		a1	17
4		a2	35
5		a3	41
6		a4	39
7		a5	12
8		a6	18
9		a7	44
10	ff73
11		f1	1
12		f2	5
13		f3	2
14		f4	4
15	bb55
16		b1	23
17		b2	24
18		b3	25
19		b4	14
20		b5	15
21		b6	50
22		b7	51
23	aa23
24		a24	8
25		a29	20
26		a34	9
גיליון1

Cell Formulas
Range	Formula
G2	G2	=INDIRECT("b"&MATCH(F2,C:C,0))

Cells with Data Validation
Cell	Allow	Criteria
F2	List	=$C$3:$C$26

 

[bebo021999]

G2:
From top

=INDEX($B$3:$B$26,MATCH(F2,$C$3:$C$26,0))

or from bottom

=INDEX($B$3:$B$26,MATCH(F2,$C$3:$C$26,0))

H2, from top

=LOOKUP("ZZZ",INDEX(A2:OFFSET(A2,MATCH(F2,$C$3:$C$26,0),),))

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I would also avoid using volatile functions like INDIRECT and OFFSET if there are viable alternatives.

IF you have MS 365 then you could replace the G2 formula with the one shown below. If no 365 then there are other non-volatile suggestions in post #2.

I have also included a non-volatile suggestion for H2

22 02 23.xlsm
	A	B	C	D	E	F	G	H
1	Group	Item	Cat. Num			Cat. Num	Item	Group
2	aa90					44	a7	aa90
3		a1	17
4		a2	35
5		a3	41
6		a4	39
7		a5	12
8		a6	18
9		a7	44
10	ff73
11		f1	1
12		f2	5
13		f3	2
14		f4	4
15	bb55
16		b1	23
17		b2	24
18		b3	25
19		b4	14
20		b5	15
21		b6	50
22		b7	51
23	aa23
24		a24	8
25		a29	20
26		a34	9
Lookup

Cell Formulas
Range	Formula
G2	G2	=XLOOKUP(F2,C:C,B:B)
H2	H2	=LOOKUP("zzz",A2:INDEX(A:A,MATCH(F2,C:C,0)))

 

[Natit]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I would also avoid using volatile functions like INDIRECT and OFFSET if there are viable alternatives.

IF you have MS 365 then you could replace the G2 formula with the one shown below. If no 365 then there are other non-volatile suggestions in post #2.

I have also included a non-volatile suggestion for H2

22 02 23.xlsm
	A	B	C	D	E	F	G	H
1	Group	Item	Cat. Num			Cat. Num	Item	Group
2	aa90					44	a7	aa90
3		a1	17
4		a2	35
5		a3	41
6		a4	39
7		a5	12
8		a6	18
9		a7	44
10	ff73
11		f1	1
12		f2	5
13		f3	2
14		f4	4
15	bb55
16		b1	23
17		b2	24
18		b3	25
19		b4	14
20		b5	15
21		b6	50
22		b7	51
23	aa23
24		a24	8
25		a29	20
26		a34	9
Lookup

Cell Formulas
Range	Formula
G2	G2	=XLOOKUP(F2,C:C,B:B)
H2	H2	=LOOKUP("zzz",A2:INDEX(A:A,MATCH(F2,C:C,0)))

Hi Peter,
The formula in G2 works perfect.
The formula in H2, gave me an error and it took me almost an hour to find out why.
My Office version is not in English so the formula gave me an error with the "zzz" letters.
When I replaced it with characters in my language, it worked great.
Thx a lot
Nati

I even simplified it (for testing) as "=lookup("zzz", a2:a25)", and it also gives me an error.

 

[Peter_SSs]

When I replaced it with characters in my language, it worked great.

Good news. Not sure what you replaced the "zzz" with but it needs to be some text that would come after any other text you might have in the column if it was sorted alphabetically.

The formula in G2 works perfect.

That indicates that you have MS 365 or later. It would still help in the future if you included that information in your account details as I suggested earlier so it was always available to helpers.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)