CODEWITHSUNDEEP
arraysbooleangoogle-sheets-formulavlookuparray-formulas
shenkwen
shenkwen

Reputation: 3880

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

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.

Upvotes: 2

Views: 5802

Answers (2)

calJersey
calJersey

Reputation: 1

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))
)

Upvotes: 0

player0
player0

Reputation: 1

try:

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

Upvotes: 1

Related Questions