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

 

[alansidman]

MS Excel: How to use the IFNA Function (WS)

This Excel tutorial explains how to use the Excel IFNA function with syntax and examples. The Microsoft Excel IFNA function returns an alternate value if a formula results in #N/A error.

www.techonthenet.com

 

[nature969]

Thank you Alan
I replaced "iferror" to IfNA" in my formula and it didn't work.

I want to see if NA (in multiple columns) show " not find" in one column only ( e. column AQ).

 

[alansidman]

Perhaps you should show us some sample data that we can work with. Use XL2BB to upload your samples. Also, show us the expected results mocked up. Not sure I understand what you mean by "NA in multiple columns."

 

[shaowu459]

Do you mean when the value is matched in the first column of the table, it returns all values

Book1
	A	B	C	D	E	F	G	H	I
1	F					Name	Value1	Value2	Value3
2						F	93	71	22
3
4
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
Sheet1

Cell Formulas
Range	Formula
F2:I2	F2	=IF(ISNUMBER(MATCH("Not Find",$E2:E2,)),"",IFNA(VLOOKUP($A1,Table2[#All],COLUMN(A1),0),"Not Find"))

if not, only the first column returns Not Find

Book1
	A	B	C	D	E	F	G	H	I
1	H					Name	Value1	Value2	Value3
2						Not Find
3
4
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
Sheet1

Cell Formulas
Range	Formula
F2:I2	F2	=IF(ISNUMBER(MATCH("Not Find",$E2:E2,)),"",IFNA(VLOOKUP($A1,Table2[#All],COLUMN(A1),0),"Not Find"))

 

[nature969]

Yes the 2nd one is what I want
I tried your formula , unsure why it's not working.

I want to see as below
H3 shows "Not Find"
I3 shows blank
J3 shows blank
K3 shows blank

 

[shaowu459]

Yes the 2nd one is what I want
I tried your formula , unsure why it's not working.
View attachment 14254

I want to see as below
H3 shows "Not Find"
I3 shows blank
J3 shows blank
K3 shows blank

View attachment 14255

Change Table1 to your own table name by select the whole table with your mouse. or change Table1 to $B$5:$B$12

 

[shaowu459]

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1						Lookup	Value1	Value2	Value3
2						C	88	5	53
3						H	Not Find
4						E	82	93	40
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
Sheet1

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

 

[nature969]

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1						Lookup	Value1	Value2	Value3
2						C	88	5	53
3						H	Not Find
4						E	82	93	40
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
Sheet1

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

Hi there
Thank you again

Can you tell me where went wrong?

 

[shaowu459]

Hi there
Thank you again

Can you tell me where went wrong?

View attachment 14899

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