VLOOKUP show Error massage in a column

[nature969]

Hi Guys

I am using the below Vlookup ( lookup one value and return multiple values). I need one more step to add on this formula to show all errors (#NA) as text " Not Find" in ONE column. Currently the formula shows "Not find" in all un-matched columns.

=iferror(vlookup($A1,Table1,column(A1),0),"Not Find")

Thanks

 

[nature969]

change Table1 to $B$5:$E$12

Same

 

[shaowu459]

I'm not sure whether IFNA is available in Excel 2016, please try this:

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1						Lookup	Value1	Value2	Value3
2						A	28	10	65
3						H	Not Find
4						G	54	36	92
5		Name	Value1	Value2	Value3
6		A	28	10	65
7		B	23	89	38
8		C	88	5	53
9		D	79	51	11
10		E	82	93	40
11		F	93	71	22
12		G	54	36	92
Sheet10

Cell Formulas
Range	Formula
G2:I4	G2	=IF(ISNUMBER(MATCH("Not Find",$F2:F2,)),"",IF(ISNA(VLOOKUP($F2,$B$5:$E$12,COLUMN(B1),0)),"Not Find",VLOOKUP($F2,$B$5:$E$12,COLUMN(B1),0)))

 

[nature969]

I'm not sure whether IFNA is available in Excel 2016, please try this:

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1						Lookup	Value1	Value2	Value3
2						A	28	10	65
3						H	Not Find
4						G	54	36	92
5		Name	Value1	Value2	Value3
6		A	28	10	65
7		B	23	89	38
8		C	88	5	53
9		D	79	51	11
10		E	82	93	40
11		F	93	71	22
12		G	54	36	92
Sheet10

Cell Formulas
Range	Formula
G2:I4	G2	=IF(ISNUMBER(MATCH("Not Find",$F2:F2,)),"",IF(ISNA(VLOOKUP($F2,$B$5:$E$12,COLUMN(B1),0)),"Not Find",VLOOKUP($F2,$B$5:$E$12,COLUMN(B1),0)))

Thanks I got it.