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.

 

[Coding4Fun]

Assuming your list of values WW098-ZK... BB107-ZK... are in Sheet1 Column A and you have your "Conditions" in Sheet2 Column A:B

In Sheet1 Column B Row 2 Enter

=VLOOKUP(MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,2),Sheet2!$A$1:$B$11,2,FALSE)

 

[and8eye]

Assuming your list of values WW098-ZK... BB107-ZK... are in Sheet1 Column A and you have your "Conditions" in Sheet2 Column A:B

In Sheet1 Column B Row 2 Enter

=VLOOKUP(MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,2),Sheet2!$A$1:$B$11,2,FALSE)

many thanks

but sometimes the condition which i had contains 3 or 4 words, your code will return #NA

please help

 

[Aladin Akyurek]

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

where Symbols are DS, Hy, etc., Numbers 1, 2, etc., and A2 houses a string like WW098-ZK-TRXF-PAB-MRX-01.

Your description is not correct: We have ZK in-between first and second dashes, not TRXF.

Maybe the following fits better what you want:

=LOOKUP(9.99999999999999E+307,SEARCH("-"&Symbols,$A2),Numbers)

 

[Coding4Fun]

=IFERROR(VLOOKUP(MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,2),Sheet2!$A$1:$B$11,2,FALSE),VLOOKUP(MID(A2,FIND("-",A2,FIND("-",A2,FIND("-",A2)+1)+1)+1,2),Sheet2!$A$1:$B$11,2,FALSE))

 

[Coding4Fun]

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

where Symbols are DS, Hy, etc., Numbers 1, 2, etc., and A2 houses a string like WW098-ZK-TRXF-PAB-MRX-01.

Your description is not correct: We have ZK in-between first and second dashes, not TRXF.

Maybe the following fits better what you want:

=LOOKUP(9.99999999999999E+307,SEARCH("-"&Symbols,$A2),Numbers)

Aladin your code intrigued me however it only seems to return 10 for both WW098-ZK-TRXF-PAB-MRX-01 and
BB107-ZK-MRCK-N-SPDB-TRC-01

Where it should have returned 10 and 6. Try using this on BB107-ZK-MRCK-N-SPDB-TRC-01 you should get 6 but your formula returns 10? Am I missing something? I created the ranges for Symbols and Numbers.

 

[Coding4Fun]

Ah I think I see why, this string contains both -MR and -TR. Maybe?

BB107-ZK-MRCK-N-SPDB-TRC-01

 

[Aladin Akyurek]

If it's meant to evaluate the substring between the second "-" and the third "-", then:

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

 

[Coding4Fun]

=IFERROR(VLOOKUP(MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,2),Sheet2!$A$1:$B$11,2,FALSE),VLOOKUP(MID(A2,FIND("-",A2,FIND("-",A2,FIND("-",A2)+1)+1)+1,2),Sheet2!$A$1:$B$11,2,FALSE))

To clarify the formula I posted in Post #5 will look at the first 2 letters of the 3rd block and return the value, if it errors because it does not find the match then it will look at the first 2 letters of the 4th block.
This string has 6 blocks we are looking at 3 THEN 4 only.

WW098-ZK-TRXF-PAB-MRX-01
Block1-Block2-Block3-Block4-Block5-Block6

 

[Coding4Fun]

If it's meant to evaluate the substring between the second "-" and the third "-", then:

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

That one seems to work but runs in to the same problem my original answer in post #2 faced - revised answer in post #5 and #9

It will error because sometimes he wants to look at the 3rd block and sometimes the 4th block.

Example: Try it with this [TABLE="width: 257"]
<tbody>[TR]
[TD="class: xl65, width: 257"]BB107-ZK-MORE-MRCK-N-SPDB-TRC-01 assuming your trying to return from MRCK in block 4[/TD]
[/TR]
</tbody>[/TABLE]