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.

 

[and8eye]

in first post i meant "if its neither V or N then nothing shown (not error)"

 

[and8eye]

please help...

 

[and8eye]

please help...

 

[and8eye]

please help...

 

[Aladin Akyurek]

A2 and downwards house the target strings.

In D2 enter and copy down:

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

In E2 enter and copy down:

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

 

[JazzSP8]

i have these strings:

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

It is a bit confusing for us I think...

blah-blah-blahCK-N-blah - What would you want to return? 2 or 3?

You have CK which gives us a "3" but then in the next sentence you say that the N should return a "2"

You also don't need to do this in a VLOOKUP - It can be done in a formula, taking it as literally 1 is the most important rule to match and 4th being the last (as your table implies) then you could do something like this;

=IF(MID(A1,18,1)="V",1,IF(MID(A1,18,1)="N",2,IF(OR(MID(A1,15,2)="CK",MID(A1,15,2)="LX"),3,IF(MID(A1,15,2)="XF",4,""))))

 

[and8eye]

A2 and downwards house the target strings.

In D2 enter and copy down:

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

In E2 enter and copy down:

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

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

with CL, LX, XF things:

with V, N things:

p/s: just tell me if above questions are not related to this forum

 

[and8eye]

It is a bit confusing for us I think...

blah-blah-blahCK-N-blah - What would you want to return? 2 or 3?

You have CK which gives us a "3" but then in the next sentence you say that the N should return a "2"

You also don't need to do this in a VLOOKUP - It can be done in a formula, taking it as literally 1 is the most important rule to match and 4th being the last (as your table implies) then you could do something like this;

=IF(MID(A1,18,1)="V",1,IF(MID(A1,18,1)="N",2,IF(OR(MID(A1,15,2)="CK",MID(A1,15,2)="LX"),3,IF(MID(A1,15,2)="XF",4,""))))

i mean i want 2 different codes for each condition

thank you but can you please give me a vlookup code?

 

[and8eye]

A2 and downwards house the target strings.

In D2 enter and copy down:

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

In E2 enter and copy down:

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

can you please give me a vlookup code? i think it would be easier for me this way