Using lookup formula

[Syedali]

Hi Friends,

I have come across this question in one interview.

I need a answer using "LOOKUP" Formula .
Have to check "Rcvd Files" are available in the "Files" column. Answer like "yes" or "no". Without using other formulas.

File attached.

 

[Deepak]

=LOOKUP(B2,$A$2:$A$9,{"true","true","true","true","true","true","true","true"})

 

[XOR LX]

=LOOKUP(B2,$A$2:$A$9,{"true","true","true","true","true","true","true","true"})

Hi.

Can you clarify the logic behind this formula? I'm a bit worried that the entries in A2:A9 aren't necessarily in alphabetical order, which may cause problems.

Regards

 

[Syedali]

=LOOKUP(B2,$A$2:$A$9,{"true","true","true","true","true","true","true","true"})

Hi Deepak,
1.Thanks for your quick response. I need answer yes or no.
2.I can't understand the formula what you did. Kindly advice it.

Thanks in advance

 

[Deepak]

That was't a answer!!

I am not sure about YES,No but you can get true/false by this.
=LOOKUP(B2,$A$2:$A$9)=B2

 

[Khalid NGO]

Hi Deepak,
1.Thanks for your quick response. I need answer yes or no.
2.I can't understand the formula what you did. Kindly advice it.

Thanks in advance

Hi Ali,
We can wrap Deepak's formula with IFERROR for Yes / No:
=IFERROR(LOOKUP(B2,$A$2:$A$9,{"Yes","Yes","Yes","Yes","Yes","Yes","Yes","Yes"}),"No")
If text is found it will return Yes, else No.

But:
1) as XOR LX mentioned, this will not give correct answer when the data is not sorted alphabetically.
2) This will cross the limit of using lookup formula only.

Regards,

 

[Syedali]

Hi Ali,
We can wrap Deepak's formula with IFERROR for Yes / No:
=IFERROR(LOOKUP(B2,$A$2:$A$9,{"Yes","Yes","Yes","Yes","Yes","Yes","Yes","Yes"}),"No")
If text is found it will return Yes, else No.

But:
1) as XOR LX mentioned, this will not give correct answer when the data is not sorted alphabetically.
2) This will cross the limit of using lookup formula only.

Regards,

Hi Khalid,

Thanks for your response.
I need a small clarification in the above formula, ie., I have to enter "yes" with count i have in "Files Column" is that correct.
If it is kindly advice any other way, if i have more than 100 values mean i can't enter 100 yes????

 

[Nebu]

Hi:

Find the attached. Please test it and let me know.

Thanks

 

[Syedali]

Hi:

Find the attached. Please test it and let me know.

Thanks

Wow. Great bro.

Thanks

 

[azumi]

Maybe something like this? Pls find the file attach

cheers

 

[Syedali]

Maybe something like this? Pls find the file attach

cheers

Hi,
My question is using "LOOKUP" forumula.

 

[NARAYANK991]

Hi Syedali / Nebu ,

You need to check whether the SEARCH function is OK for your requirements , since this will return TRUE even if the received file name in column B is part of the file name in column A ; thus a received file name of BCB will match a file name in column A of BCBS.

If an exact match is required , you can use :

=IF(ISTEXT(C2),IFERROR(IF(LOOKUP(2^15,MATCH($B$2:$B$9,C2,0)),"Yes"),"No"),"")

Narayan

 

[Syedali]

Hi Syedali / Nebu ,

You need to check whether the SEARCH function is OK for your requirements , since this will return TRUE even if the received file name in column B is part of the file name in column A ; thus a received file name of BCB will match a file name in column A of BCBS.

If an exact match is required , you can use :

=IF(ISTEXT(C2),IFERROR(IF(LOOKUP(2^15,MATCH($B$2:$B$9,C2,0)),"Yes"),"No"),"")

Narayan

Thanks Narayan.