Search

[chrisb182]

Hi,

Sorry - but I have another query.

Again, it's a question I've searched on the forum but to no avail.

I want to search a number of columns for a specific code and for it to return the value in the corresponding row.

For example, as shown in the attached screenshot I would like:
- a formula in H4
- that would look up the text from G4
- in the range C4:E9
- and for it to return the relevant text in range B4:B9

Note: As the codes are random yet unique, the figures for one code could appear in more than one cell (as shown in the example, the numbers 1234 can be found in cells C4, C5, D6 and D8 as well)...... in a nutshell, I need an exact match.

I hope that makes sense.

Cheers,

Chris

 

[Fluff]

How about

=INDEX($B$4:$B$9,AGGREGATE(15,6,(ROW($B$4:$B$9)-ROW($B$4)+1)/($C$4:$E$9=H4),1))

 

[chrisb182]

Hi,

Thanks for the swift response.

The attached example was a small snapshot of the information I'm dealing with.

There are loads of codes going down in column G and when I entered in the above formula and dragged it down in column H, it brought up the same answer all the way down. Is there anything that I'm missing here?

What does the "AGGREGATE (15,6," part of the formula mean?

(Oh - and did you mean "=G4" in the above formula?)

 

[Fluff]

did you mean "=G4"

Yes it should be.

when I entered in the above formula and dragged it down in column H, it brought up the same answer all the way down

In that case please some realistic data, preferably using the XL2BB add-in, rather than an image.

 

[chrisb182]

I hope this works:

mR eXCEL.xlsx
	A	B	C	D	E	F	G
1	Name	Code1	Code2	Code3		Code	Name
2	Andy	AA1234X2	567	281		87027TNN
3	Sam	AA1234X3				IBHN9U2
4	Kevin	QT567TF	AA1234X4			AFANG80U
5	Lisa	CJ17085A				567
6	Pete	99RBFB	AA1234X4			567
7	Faye	MC44489	CM44578T	1234		SEDDY69
8	Tony	AFANG80U	VSNKBW			AA1234X2
9	Vicki	VBUH7980				AA1234X4
10	Jack	KSVK88	NBKSDFBH7			SRHOE99
11	Carl	VSN8SI111G	BBBB999	SEDDY72		AA1234X4
12	Selina	ABDFKR399	87027TNN			ABDFKR399
13	Arthur	2TU304N	SEDDY69			281
14	Lincoln	IBHN9U2				87027TNN
15	Elesha	SRHOE99				1234
16						SEDDY69
17						MC44489
18						BBBB999
19						AA1234X4
20						1234
21						IBHN9U2
22						IBHN9U2
23						AA1234X4
24						SRHOE99
25						281
26						AA1234X2
27						1234
28						AFANG80U
29						CM44578T
30						87027TNN
31						SEDDY69
32						VSN8SI111G
33						AA1234X3
34						SEDDY72
35						SEDDY72
36						MC44489
37						AA1234X2
38						1234
39						SRHOE99
40						567
41						281
42						1234
43						AA1234X4
44						87027TNN
45						567
46						VSNKBW
47						SEDDY72
48						VSNKBW
49						VSN8SI111G
50						ABDFKR399
51						KSVK88
52						NBKSDFBH7
53						567
54						87027TNN
55						281
56						QT567TF
57						ABDFKR399
58						SEDDY69
59						AA1234X2
60						NBKSDFBH7
61						VSNKBW
62						AA1234X3
63						AA1234X4
64						QT567TF
65						ABDFKR399
66						VSN8SI111G
67						AFANG80U
68						AA1234X4
69						CM44578T
70						2TU304N
71						87027TNN
72						BBBB999
73						99RBFB
74						SEDDY69
75						AA1234X3
76						1234
77						AA1234X2
78						SEDDY69
79						99RBFB
80						AFANG80U
81						281
82						NBKSDFBH7
83						QT567TF
84						NBKSDFBH7
85						ABDFKR399
86						AA1234X4
87						AA1234X4
88						AA1234X3
89						NBKSDFBH7
90						MC44489
91						QT567TF
92						ABDFKR399
93						AA1234X2
94						2TU304N
95						AA1234X4
96						567
97						1234
98						CJ17085A
99						567
100						AA1234X3
101						VSN8SI111G
102						99RBFB
103						NBKSDFBH7
104						99RBFB
105						281
106						NBKSDFBH7
107						IBHN9U2
108						KSVK88
109						567
110						281
111						BBBB999
112						281
113						VSNKBW
114						KSVK88
115						AA1234X4
116						567
117						1234
118						NBKSDFBH7
119						ABDFKR399
120						VSNKBW
121						IBHN9U2
122						IBHN9U2
123						1234
124						SRHOE99
125						SEDDY69
126						AA1234X3
127						567
128						AA1234X4
129						QT567TF
Sheet1

 

[Fluff]

Thanks for that. What should G6 display as that is the 2nd 567 & there is only one cell with that value in columns B:D?

 

[shinigamilight]

Try this ctrl shift enter

=INDEX($A$2:$A$15,MAX(--($B$2:$D$15=F2)*(ROW($A$2:$A$15)-1)))

 

[Tom.Jones]

Try this ctrl shift enter

=INDEX($A$2:$A$15,MAX(--($B$2:$D$15=F2)*(ROW($A$2:$A$15)-1)))

Your formula gave wrong result for ab100 or aa214MM (For codes that do not exist)

 

[chrisb182]

Thanks for that. What should G6 display as that is the 2nd 567 & there is only one cell with that value in columns B:D?

The name can appear more than once in column G, so 'Andy' should appear in both G5 and G6.

 

[Fluff]

Ok, how about

=IFERROR(INDEX($A$2:$A$15,AGGREGATE(15,6,(ROW($A$2:$A$15)-ROW($A$2)+1)/($B$2:$D$15=F2),COUNTIFS(F$2:F2,F2))),INDEX($A$2:$A$15,AGGREGATE(15,6,(ROW($A$2:$A$15)-ROW($A$2)+1)/($B$2:$D$15=F2),1)))