Find a specified text

[riyazpattan619]

Hi Friends,

Seeking your attention on the below scenario.

Scenario 1: This Condition is working...
=LOOKUP(A2,{"DIAMOND","PREMIERE PLUS","PLATINUM","PREMIERE"},{"DIAMOND","PREMIERE PLUS","PLATINUM","PREMIERE"})

Scenario 2: This condition is not working.
=LOOKUP(A2:A20,{"DIAMOND","PREMIERE PLUS","PLATINUM","PREMIERE"},{"DIAMOND","PREMIERE PLUS","PLATINUM","PREMIERE"})

I do hope that by looking at the scenario you may understand what I mean to expect. Rather providing the whole story here, would like to give a brief understanding here.

"I'm looking for second scenario to be worked, where in a column range if "Diamond" appears, then the same should populate, and same for the rest. No chance of any two or three appearing in the column.

Appreciate your valuable time.

Riyaz

 

[shrivallabha]

Perhaps you are looking for something on following lines:

=LOOKUP(2,1/COUNTIF(A2:A20,{"DIAMOND","PREMIERE PLUS","PLATINUM","PREMIERE"}),{"DIAMOND","PREMIERE PLUS","PLATINUM","PREMIERE"})

 

[shrivallabha]

=MATCH({"DIAMOND","PREMIERE PLUS","PLATINUM","PREMIERE"},A2:A20,0)

I tried this formula by entering normally as well as by CSE but doesn't seem to work either way. Have you checked it?

 

[chirayu]

I tried this formula by entering normally as well as by CSE but doesn't seem to work either way. Have you checked it?

Yeah it was working then I somehow messed it up & posted it here so deleted it, gonna retry lol

 

[Khalid NGO]

Hi Riyaz,

I may have missed your question, but I think your formula is doing the same thing as these:

=VLOOKUP("*",A2:A20,1,0)
=INDEX(A2:A20,MATCH("*",A2:A20,0))

You can also add a check something like:

=IF(SUM(COUNTIF(A2:A20,{"DIAMOND","PREMIERE PLUS","PLATINUM","PREMIERE"}))>0, VLOOKUP("*",A2:A20,1,0),"")

Regards,

 

[riyazpattan619]

Perhaps you are looking for something on following lines:

=LOOKUP(2,1/COUNTIF(A2:A20,{"DIAMOND","PREMIERE PLUS","PLATINUM","PREMIERE"}),{"DIAMOND","PREMIERE PLUS","PLATINUM","PREMIERE"})

This one is quite working. Thank you Shrivallabha.

 

[riyazpattan619]

Hi Riyaz,

I may have missed your question, but I think your formula is doing the same thing as these:

=VLOOKUP("*",A2:A20,1,0)
=INDEX(A2:A20,MATCH("*",A2:A20,0))

You can also add a check something like:

=IF(SUM(COUNTIF(A2:A20,{"DIAMOND","PREMIERE PLUS","PLATINUM","PREMIERE"}))>0, VLOOKUP("*",A2:A20,1,0),"")

Regards,

My Friend Khalid,

The below one is working.As suggested by Mr. Shrivallabha.

=LOOKUP(2,1/COUNTIF(A2:A20,{"DIAMOND","PREMIERE PLUS","PLATINUM","PREMIERE"}),{"DIAMOND","PREMIERE PLUS","PLATINUM","PREMIERE"})

Thank you for your inputs.

Riyaz