Indexing the number

[sachar]

Dear All,

With reference to the attach sample file, the [column “A”] Text contained the words like: “Tax”, “Service” and “Tips” on that basis, I would like to give the number 1=Tax, 2=Service, 3=Tips in the next [column “c”]

Please help me to sort out my problems?

 

[Khalid NGO]

Hi,

Not too fancy but it works:

=IF(COUNTIF(A1,"*tax*"),1,IF(COUNTIF(A1,"*service*"),2,IF(COUNTIF(A1,"*tips*"),3,"")))

Regards,

 

[NARAYANK991]

Hi ,

Another possibility :

=MATCH(1,--ISNUMBER(MATCH("*" & MATCH_TABLE & "*",A1,0)),0)

entered as an array formula , using CTRL SHIFT ENTER.

MATCH_TABLE refers to the range ='trial_balance11-12-16'!$H$1:$H$3.

Narayan

 

[bosco_yip]

Another option :

=IFERROR(4-MATCH(99,SEARCH({"tips";"service ";"tax"},A1)),"")

Regards
Bosco

 

[Derek McGill]

Another option :

=IFERROR(4-MATCH(99,SEARCH({"tips";"service ";"tax"},A1)),"")

Regards
Bosco

Can you talk through how this works ?

 

[Norm Dziedzic]

One More option. Put this in cell I1 and then copy drag down:

=COUNTIF($A$1:$A$78,"*" & H1 & "*")

Or, if you create a table with your data called Charge_Names you can do this:
=COUNTIF(Charge_Names,"*" & H1 & "*")
The advantage of using a named table is that when you add data, the total counts will be updated.

 

[Nebu]

Hi:

Another Option:
=IFERROR(LOOKUP(2^15,SEARCH($H$1:$H$3,A1),$I$1:$I$3),"")

Thanks

 

[bosco_yip]

Can you talk through how this works ?

Hi,
Using Binary Search and Bignum of MATCH function to find the position of the last value within a given ref.

In this example

=IFERROR(4-MATCH(99,SEARCH({"tips";"service ";"tax"},A1)),"")

In here, "99" is the Bignum and you can use the biggest number 9.9999999999999E+307 instead.

>>

=IFERROR(4-MATCH(99,{#VALUE!;16;#VALUE!}),"")

By the Binary Search of MATCH, it will give the position of the last value

>>

=IFERROR(4-2,"")

"2" is the position of the last value.

>>

=2

Desired result.


Edit : Since, the Binary search is searching bottom-up, the using of 4-MATCH(…..) is to meet with the OP's required order.

Regards
Bosco

 

[Norm Dziedzic]

One More option. Put this in cell I1 and then copy drag down:

=COUNTIF($A$1:$A$78,"*" & H1 & "*")

Or, if you create a table with your data called Charge_Names you can do this:
=COUNTIF(Charge_Names,"*" & H1 & "*")
The advantage of using a named table is that when you add data, the total counts will be updated.

I read your issue wrong. Please disregard this. Sorry for the confusion

 

[Vishal Srivastava]

My Approach:

=AGGREGATE(15,6,ROW($H$1:$H$3)*1/(FIND($H$1:$H$3,A1)>0),1)


Thanks & Regards,
CMA Vishal Srivastava