Too many arguments for function

[Bandito1]

Hi all,

I use the following formula to check if a number is in a list.
The number can be with or without a "G" infront

=IF(A3="";"";IF(ISNA(VLOOKUP(SUBSTITUTE(A3;"G";"")+0;'2016-2022'!B:B;1;FALSE));"No";"Yes"))

Now i try to rebuild this formula to show the value in column 9. If there is nothing in that column it should show "no comments"

When i use =IFERROR(VLOOKUP(A3;'2016-2022'!B:J;9;FALSE);"No comments") it works but it doesn't work if there is a G infront of the number.

Now i tried IF(A4="";"";IF(ISNA(VLOOKUP(SUBSTITUTE(A4;"G";"")+0;'2016-2022'!B:J;0;FALSE));"No comments";"") but now i get no value if there is a value in the column. It's always ""

Excel keeps going on not responding when i use xl2bb on the file.
Sorry

 

[Peter_SSs]

Try

=IF(A4="";"";IFNA(VLOOKUP(SUBSTITUTE(A4;"G";"")+0,'2016-2022'!B:J;9;FALSE),"No comments"))

 

[Bandito1]

That works, thank you

1 small thing; it shows "0" instead of "no comments" when the number is in the list
If the number isn't in the list it shows "no comments"..

In both cases it should no comments if there are no comments.

 

[Peter_SSs]

it shows "0" instead of "no comments" when the number is in the list

.. only if the column J cell is empty for that row.

Assuming that any values in column J are text and not numbers, try

=IF(A4="";"";IFNA(VLOOKUP(SUBSTITUTE(A4;"G";"")+0,'2016-2022'!B:J;9;FALSE)&"","No comments"))

 

[Bandito1]

Now it only shows only "no comments" if the number is not in the list

It leaves the cell blank if the number is in the list

 

[Peter_SSs]

That is not the case for me. here is my sample data and results (note my system uses comma separators in formulas).
If you can't find what is wrong, perhaps you could post your small sample of each sheet with like I have so that we can investigate?

22 11 30.xlsm
	B								J
1	Lookup Num								Data
2	123								data 1
3	124								data 2
4	125								data 3
5	126								data 4
6	127								data 5
7	128								data 6
8	129								data 7
9	130								data 8
10	131								data 9
11	132
12	133
13	134
14	135								data 13
2016-2022

Are these the results that you would want from my sample data?

22 11 30.xlsm
	A	B
4	G123	data 1
5	134
6	200	No comments
7
8	125	data 3
9	G350	No comments
10	127	data 5
11	G127	data 5
Sheet5

Cell Formulas
Range	Formula
B4:B11	B4	=IF(A4="","",IFNA(VLOOKUP(SUBSTITUTE(A4,"G","")+0,'2016-2022'!B:J,9,FALSE)&"","No comments"))