Help with MID+FIND formula

[Bering]

Hello,

I would need some help with a formula I am trying to build.

Please refer to the file in the link as I think it would be much easier to understand what I am trying to achieve.

https://www.dropbox.com/s/tt1r2ut325qkzdq/Test.xlsx?dl=0

The formula in cell C1 should evaluate a string in cell A2 and return "Gross" or "Net" depending on the conditions set in the table in range F1 - J4.

However, if the combination of 1 or 2 digits appears at the beginning of the string, the formula should return N/A.

I managed to accomplish the first part (Gross, Net) but I am really struggling with the N/A part.

Any ideas?

Many thanks!!

 

[Aladin Akyurek]

It's difficult to see what the data is in order to obtain C1. What follows is your dropbox file as is:

Book1
	A	B	C	D	E	F	G	H	I	J
1	AS2		Gross			M	Q	S	D	NET
2						M1	Q1	S1	D1	GROSS
3						M2	Q2	S2	D2	GROSS
4						M3	Q3	S3	D3	GROSS
5
6
7	Expected results
8	L1MH	L1QH	L1SH	L1DH	Net
9	L1M1	L1Q1	L1S1	L1D1	Gross
10	AM2	AQ2	AS2	AD2	Gross
11	AM3	AQ3	AS3	AD3	Gross
12	M1H	Q3O	S1H	D2	Not applicable
Sheet1

 

[Bering]

Hello,

thanks for looking into my query. I thought the file could also be downloaded as an excel file from dropbox.

I paste below the formula in C1, hope it helps:

IF(OR(IFERROR(MID(A1,FIND(G2,A1),2)=G2,0),IFERROR(MID(A1,FIND(G3,A1),2)=G3,0),IFERROR(MID(A1,FIND(G4,A1),2)=G4,0),IFERROR(MID(A1,FIND(F2,A1),2)=F2,0),IFERROR(MID(A1,FIND(F3,A1),2)=F3,0),IFERROR(MID(A1,FIND(F4,A1),2)=F4,0),IFERROR(MID(A1,FIND(H2,A1),2)=H2,0),IFERROR(MID(A1,FIND(H3,A1),2)=H3,0),IFERROR(MID(A1,FIND(H4,A1),2)=H4,0),IFERROR(MID(A1,FIND(I2,A1),2)=I2,0),IFERROR(MID(A1,FIND(I3,A1),2)=I3,0),IFERROR(MID(A1,FIND(I4,A1),2)=I4,0)),"Gross","Net")

It's difficult to see what the data is in order to obtain C1. What follows is your dropbox file as is:

	A	B	C	D	E	F	G	H	I	J
AS2	M	Q	S	D	NET
M1	Q1	S1	D1	GROSS
M2	Q2	S2	D2	GROSS
M3	Q3	S3	D3	GROSS
L1MH	L1QH	L1SH	L1DH	Net
L1M1	L1Q1	L1S1	L1D1	Gross
AM2	AQ2	AS2	AD2	Gross
AM3	AQ3	AS3	AD3	Gross
Not applicable

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #D9E1F2"]Gross[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Expected results[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFC000"]M1H[/TD]
[TD="bgcolor: #FFC000"]Q3O[/TD]
[TD="bgcolor: #FFC000"]S1H[/TD]
[TD="bgcolor: #FFC000"]D2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

 

[Bering]

Apologies for the double post but I do not know how to edit the previous one....

Here is some more guidance:

The possible strings to be evaluated by the formula are listed in range A8 - D12. The expected result for each of them is in range E8 - E12.

The conditions to be met are in range F1 - J4: for example, if the string contains M,Q,S or D (but not in the first position) than formula should return NET; if the string contains M1, Q2 or D3 the result should be GROSS.
However, if any of those combinations appear at the beginning of the string (as shown in row 12) then the formula should return "Not Applicable".

I hope this helps. Thanks!!!

 

[Aladin Akyurek]

You should not post a (non-working) formula. Try instead to explain the parts of Sheet1 I posted from your file. For one thing, how do you obtain the value in C1 for the value you have in A1. What is asked for a manual procedure that leads to C1, not a (non-working) formula.

 

[Bering]

Sorry for that. I thought it would have been helpful to start from my attempt rather than from scratch. The formula works partially, there is one extra bit I would like to accomplish for which I need your help.

However, please note that I had already added some more details in the post above sent @ 11:38AM, which I think explain how the Sheet works.

Is that not helpful? If so, please let me know and I will try my best to clarify my query.

Many thanks.

You should not post a (non-working) formula. Try instead to explain the parts of Sheet1 I posted from your file. For one thing, how do you obtain the value in C1 for the value you have in A1. What is asked for a manual procedure that leads to C1, not a (non-working) formula.