vlookup question 2

[and8eye]

sorry for creating another thread but in previous thread i forgot to mention this (i also tried but i failed)

i have these strings:

blah-blah-blahCK-N-blah
blah-blah-blahLX-N-blah
blah-blah-blahXF-V-blah

and i have these "conditions" (2 different's) in "vlookup conditions" sheet:

please help me with 2 different codes which:
- if 2 last words of the 3rd block (the block between the 2nd dash and the 3rd dash, from left) are CK (or LX) then 3 (if theyre XF then 4)
- if the 4th block (the block between the 3rd dash and the 4th dash, from left) is V (or N) then 1 (or 2) ---> if its either V or N then nothing show (not error)

thank you all.

 

[JazzSP8]

Does this work for you then?

=IFERROR(VLOOKUP(MID(G1,15,2),$S$1:$T$3,2,FALSE),"")&IFERROR(VLOOKUP(MID(G1,18,1),$P$1:$Q$2,2,FALSE),"")

 

[and8eye]

Does this work for you then?

=IFERROR(VLOOKUP(MID(G1,15,2),$S$1:$T$3,2,FALSE),"")&IFERROR(VLOOKUP(MID(G1,18,1),$P$1:$Q$2,2,FALSE),"")

thank you but its not always the 15th or 18th word from left, sometimes it is the 10th, 12th, 13th, ...

 

[JazzSP8]

thank you but its not always the 15th or 18th word from left, sometimes it is the 10th, 12th, 13th, ...

But. That's not what you said originally...

OK - Looking at the screen shot you posted, does the "HK" before the CK, LX or XF?

Is there anything else we should know?

 

[and8eye]

But. That's not what you said originally...

OK - Looking at the screen shot you posted, does the "HK" before the CK, LX or XF?

Is there anything else we should know?

my bad, sorry

CK, LX or XF position is always the last 2 words of the third block (between the second dash and the third dash)

*for the V and N things, can it be something like this?
- if string contains -V- then 1, or if string contains -N- then 2, or if string contains neither -V- nor -N- then error

 

[JazzSP8]

my bad, sorry

No worries - Just try and remember to include proper data samples so people know what they're working with

CK, LX or XF position is always the last 2 words of the third block (between the second dash and the third dash)

*for the V and N things, can it be something like this?
- if string contains -V- then 1, or if string contains -N- then 2, or if string contains neither -V- nor -N- then error

Try this;

=IFERROR(VLOOKUP(RIGHT(MID(G1,FIND("-",G1,FIND("-",G1)+2),5),2),S1:T3,2,FALSE),"")&VLOOKUP(RIGHT(MID(G1,FIND("-",G1,FIND("-",G1)+2),7),1),P1:Q2,2,FALSE)

Any use?

 

[and8eye]

No worries - Just try and remember to include proper data samples so people know what they're working with



Try this;

=IFERROR(VLOOKUP(RIGHT(MID(G1,FIND("-",G1,FIND("-",G1)+2),5),2),S1:T3,2,FALSE),"")&VLOOKUP(RIGHT(MID(G1,FIND("-",G1,FIND("-",G1)+2),7),1),P1:Q2,2,FALSE)

Any use?

thank you it works but...

with strings like this it shows error:

LF002-ZK-600WCK-N-HXB-QGM-03
LF002-ZK-361CK-N-HXB-QGM-03
LF002-ZK-QJCCK-N-HXB-QGM-03

 

[Aladin Akyurek]

thank you very much, this works perfectly in excel but when i paste this on google sheets (sorry for not mentioning it earlier), it throws me error

[...]

D2, copied down:

=ARRAYFORMULA(LOOKUP(9.99999999999999E+307,SEARCH({"CK","LX","XF"}&"-",REPLACE(A2,1,FIND("|",SUBSTITUTE(A2,"-","|",2)),"")),{3,3,4}))

E2, copied down:

=ARRAYFORMULA(LOOKUP(9.99999999999999E+307,SEARCH({"V","N"}&"-",REPLACE(A2,1,FIND("|",SUBSTITUTE(A2,"-","|",3)),"")),{1,2}))

 

[and8eye]

@Aladin Akyurek @JazzSP8

you both are god, thanks men