Filter Formula

[ronie85]

Hi, I requrie a formula in sheet 2 cell AW2 through to AW99 to search using the data shown in Sheet 2 Cell A2 (to A99 as it will repeat on each row)

This will then search for the same data in Sheet 1 Cell C2 to C99, and return the first occurance of the value shown within Column N that matches the same row (N1 to N99).

I would assume this is a =filter formula but hoping there is something simple as one I had tried started thread calculations and froze the workbook.

Thanks in advance

 

[Fluff]

How about

=XLOOKUP(A2,Sheet1!C2:C99,Sheet1!N2:N99,"No match")

 

[felixstraube]

Is this what you need?:

Sheet 1

SimpleLookup.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N
1
2	A											AAA
3	B											BBB
4	C											CCC
5	D											DDD
6	E											EEE
7	F											FFF
8	G											GGG
9	H											HHH
10	I											III
11	J											JJJ
12	K											KKK
13	L											LLL
14	M											MMM
15	N											NNN
16	O											OOO
17	P											PPP
18	Q											QQQ
19	R											RRR
20	S											SSS
21	T											TTT
22	U											UUU
23	V											VVV
24	W											WWW
25	X											XXX
26	Y											YYY
Sheet 1

And in Sheed 2

SimpleLookup.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW
1
2	J																																																JJJ
3	E																																																EEE
4	L																																																LLL
5	N																																																NNN
6	L																																																LLL
7	X																																																XXX
8	R																																																RRR
9	C																																																CCC
10	S																																																SSS
11	Z																																																#N/D
12	Q																																																QQQ
13	N																																																NNN
14	G																																																GGG
15	A																																																AAA
16	X																																																XXX
17	F																																																FFF
18	D																																																DDD
19	X																																																XXX
20	L																																																LLL
21	B																																																BBB
22	F																																																FFF
23	N																																																NNN
24	K																																																KKK
25	K																																																KKK
26	S																																																SSS
27	X																																																XXX
28	D																																																DDD
29	O																																																OOO
Sheet 2

Cell Formulas
Range	Formula
AW2:AW29	AW2	=INDEX('Sheet 1'!$N$2:$N$99, MATCH(A2,'Sheet 1'!$C$2:$C$99,0))

 

[ronie85]

How about

=XLOOKUP(A2,Sheet1!C2:C99,Sheet1!N2:N99,"No match")

This worked as required, thanks a lot

 

[Fluff]

You're welcome & thanks for the feedback.