vlookup question

[and8eye]

(please dont delete this thread, i cant think of a better title)

hi everyone,

i have strings (each cell contains) like this:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>WW098-ZK-TRXF-PAB-MRX-01
<style type="text/css"></style>BB107-ZK-MRCK-N-SPDB-TRC-01

​

and i have condition like this:

i want to: "if strings between the first - (dash) and the second - (dash) (from left, in this case theyre TRXF and MRCK) matches the condition i had, then print it out"

it should be something like: "if -TRXF- contains TR then 10, or if -MRCK- contains MR then 6"

thank you.

 

[and8eye]

thank you all

sorry for my bad english and description (i cant edit the first post anymore)

i meant i always want to look at the 3rd block (only the 3rd block, the block between the second dash and the third dash)

sometimes those strings would be like this:
blah-blah-600Wblah-blah-blah-blah
blah-blah-TRblah-blah-blah-blah
blah-blah-QJCblah-blah-blah-blah

so it would be if first 2 words (or 3 words, or 4 words, or n words) of the 3rd block matches the condition i had, then print it

thanks

 

[Aladin Akyurek]

thank you all

sorry for my bad english and description (i cant edit the first post anymore)

i meant i always want to look at the 3rd block (only the 3rd block, the block between the second dash and the third dash)

sometimes those strings would be like this:
blah-blah-600Wblah-blah-blah-blah
blah-blah-TRblah-blah-blah-blah
blah-blah-QJCblah-blah-blah-blah

so it would be if first 2 words (or 3 words, or 4 words, or n words) of the 3rd block matches the condition i had, then print it

thanks

It's not clear what you mean by "print it"...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td]Symbols[/td][td]Numbers[/td][/tr]
[tr][td]

2​

[/td][td]WW098-ZK-TRXF-PAB-MRX-01[/td][td]

10​

[/td][td][/td][td][/td][td]DS[/td][td]

1​

[/td][/tr]
[tr][td]

3​

[/td][td]BB107-ZK-MRCK-N-SPDB-TRC-01[/td][td]

6​

[/td][td][/td][td][/td][td]HY[/td][td]

2​

[/td][/tr]
[tr][td]

4​

[/td][td]blah-blah-600Wblah-blah-blah-blah[/td][td]

7​

[/td][td][/td][td][/td][td]YB[/td][td]

3​

[/td][/tr]
[tr][td]

5​

[/td][td]blah-blah-TRblah-blah-blah-blah[/td][td]

10​

[/td][td][/td][td][/td][td]QJC[/td][td]

4​

[/td][/tr]
[tr][td]

6​

[/td][td]blah-blah-QJCblah-blah-blah-blah[/td][td]

4​

[/td][td][/td][td][/td][td]HR[/td][td]

5​

[/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td]MR[/td][td]

6​

[/td][/tr]
[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td]600W[/td][td]

7​

[/td][/tr]
[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td]BN[/td][td]

8​

[/td][/tr]
[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td]

361​

[/td][td]

9​

[/td][/tr]
[tr][td]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td]TR[/td][td]

10​

[/td][/tr]
[tr][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td]DT[/td][td]

11​

[/td][/tr]
[/table]

As said earlier, in B2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH("|"&Symbols,"|"&REPLACE(A2,1,FIND("I",SUBSTITUTE($A2,"-","I",2)),"")),Numbers)

where Symbols refers to E2:E12 and Numbers to F2:F12.

 

[and8eye]

It's not clear what you mean by "print it"...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Symbols[/TD]
[TD]Numbers[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]WW098-ZK-TRXF-PAB-MRX-01[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]DS[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]BB107-ZK-MRCK-N-SPDB-TRC-01[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]HY[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]blah-blah-600Wblah-blah-blah-blah[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]YB[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]blah-blah-TRblah-blah-blah-blah[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]QJC[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]blah-blah-QJCblah-blah-blah-blah[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]HR[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MR[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]600W[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BN[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

361​

[/TD]
[TD]

9​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TR[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DT[/TD]
[TD]

11​

[/TD]
[/TR]
</tbody>[/TABLE]


As said earlier, in B2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH("|"&Symbols,"|"&REPLACE(A2,1,FIND("I",SUBSTITUTE($A2,"-","I",2)),"")),Numbers)

where Symbols refers to E2:E12 and Numbers to F2:F12.

thank you very much but it shows me error

 

[Aladin Akyurek]

You didn't define Symbols and Numbers as I suggested. Here is the formula without those names...

=LOOKUP(9.99999999999999E+307,SEARCH("|"&$E$2:$E$12,"|"&REPLACE($A2,1,FIND("I",SUBSTITUTE($A2,"-","I",2)),"")),$F$2:$F$12)

 

[markmzz]

Hi!

Another way:

=LOOKUP(1,SEARCH($E$1:$E$11,TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",99)),2*99,99))),$F$1:$F$11)

[TABLE="class: grid, width: 346"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]WW098-ZK-TRXF-PAB-MRX-01[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]DS[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BB107-ZK-MRCK-N-SPDB-TRC-01[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]HY[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]WW098-ZK-HRXF[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]YB[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BB107-ZK-600WCK-N[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]QJC[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]WW098-ZK[/TD]
[TD]#N/D[/TD]
[TD][/TD]
[TD][/TD]
[TD]HR[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]BB107-ZK-YBCKMR[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]MR[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]BB107-ZK-MORE-MRCK-N-SPDB-TRC-01[/TD]
[TD]#N/D[/TD]
[TD][/TD]
[TD][/TD]
[TD]600W[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]blah-blah-600Wblah-blah-blah-blah[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]BN[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]blah-blah-TRblah-blah-blah-blah[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]361[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]blah-blah-QJCblah-blah-blah-blah[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]TR[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]************************************[/TD]
[TD]******[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]*****[/TD]
[TD]****[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

 

[Aladin Akyurek]

Edit...

The formula seems to have the capital i instead of the bar symbol...

=LOOKUP(9.99999999999999E+307,SEARCH("|"&$E$2:$E$12,"|"&REPLACE($A2,1,FIND("|",SUBSTITUTE($A2,"-","|",2)),"")),$F$2:$F$12)