Google Sheet Formula: How to convert a string to Bool value?

user_2302661 0
Viewed 1383

In Google Sheet strings are deemed as False while numbers True.

=OR(FALSE,A1)

If A1 is a number other than 0, then it returns TRUE; if it is a string, then it returns False.

My real case is that I am using VLOOKUP to check if payer_info(email and phone) exists in our database

=OR(
IFERROR(VLOOKUP(payer_phone,'student_list'!C:C,1,false),false), 
IFERROR(VLOOKUP(payer_email,'student_list'!D:D,1,false),false)
)

I expect the above formula to return TRUE if payer_email exists in 'student_list'!D:D or payer_phone exists in 'student_list'!C:C. However, IFERROR(VLOOKUP(payer_email,'student_list'!D:D,1,false),false) will be deemed as FALSE in the OR function because it returns the email string.

So, is there any formula I can use to convert a not empty string to TRUE?

what_function(IFERROR(VLOOKUP(payer_email,'student_list'!D:D,1,false),false))

If VLOOKUP does find the value then it should return TRUE, if not it returns FALSE.

vlookup array-formulas google-sheets-formula boolean arrays
2 Answers

try:

=((IFERROR(VLOOKUP(payer_phone, 'student_list'!C:C, 1, 0))="")+ 
  (IFERROR(VLOOKUP(payer_email, 'student_list'!D:D, 1, 0))=""))=0
user_56326290

Evaluate the length of what's returned:

=OR(LEN(IFERROR(VLOOKUP(payer_phone,'student_list'!C:C,1,false)), 
LEN(IFERROR(VLOOKUP(payer_email,'student_list'!D:D,1,false))
)
user_178739680
Related