numbers with a "G" infront (sometimes) leave out the G

[Bandito1]

Hello,

I use the following formula to check if numbers are in a list or not;

=IF(ISNA(VLOOKUP(K9;'2016-2022'!B:B;1;FALSE));"No";"Yes")

Sometimes the number contains a G infront. The G should not be seen.

G333114 should be 333114

But not always the G is infront.

Is there a way to add something that IF there is a G infront it should be not looked at?

 

[ExceLoki]

try this
-------------

Book1
	B	C	D	E	F	G	H	I	J	K	L
1	231									123	Yes
2	g123									231	Yes
3	g456									456	Yes
4	889									888	No
5	g765									765	Yes
6	g147									147	Yes
7	147									982	No
8	356									357	No
Sheet1

Cell Formulas
Range	Formula
L1:L8	L1	=IF(ISNA(VLOOKUP(K1,IF(ISNUMBER(B:B),B:B,VALUE(RIGHT(B:B,LEN(B:B)-1))),1,FALSE)),"No","Yes")

 

[Fluff]

Another option

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

 

[ExceLoki]

is the occasional "G" in column '2016-2022'!B or column K?

 

[ExceLoki]

if the occasional "G" is in K, then
--------------

numbers with a G infront sometimes leave out the G-lookup trim.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	123									231	Yes
2	231									g123	Yes
3	456									g456	Yes
4	888									889	No
5	765									g765	Yes
6	147									g147	Yes
7	982									356	No
8	357									982	Yes
g in K

Cell Formulas
Range	Formula
L1:L8	L1	=IF(ISNA(VLOOKUP(IF(ISNUMBER(K1),K1,VALUE(RIGHT(K1,LEN(K1)-1))),B:B,1,FALSE)),"No","Yes")

 

[Bandito1]

Another option

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

The value stays "Yes" if there is no text in the lookup column, can it be blank if there is no value entered?

 

[Fluff]

How about

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

 

[Bandito1]

if the occasional "G" is in K, then
--------------

numbers with a G infront sometimes leave out the G-lookup trim.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	123									231	Yes
2	231									g123	Yes
3	456									g456	Yes
4	888									889	No
5	765									g765	Yes
6	147									g147	Yes
7	982									356	No
8	357									982	Yes
g in K

Cell Formulas
Range	Formula
L1:L8	L1	=IF(ISNA(VLOOKUP(IF(ISNUMBER(K1),K1,VALUE(RIGHT(K1,LEN(K1)-1))),B:B,1,FALSE)),"No","Yes")

Works also
But also, the value stays "Yes" if there is no value entered in the lookup column

 

[Bandito1]

How about

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

That works! thank you very much!

 

[ExceLoki]

The value stays "Yes" if there is no text in the lookup column, can it be blank if there is no value entered?

=IF(K1="","",IF(ISNA(VLOOKUP(IF(ISNUMBER(K1),K1,VALUE(RIGHT(K1,LEN(K1)-1))),B:B,1,FALSE)),"No","Yes"))