Serch be fragment of text (Vloocup ? macro ?)

Hi,

maybe in G25

Please Login or Register  to view this content.

Hope it helps

hi there. do add a 0 or FALSE at the end of your VLOOKUP formula to find an exact match. you're playing with fire to use approximate match (without the 0 or FALSE). you can do a google search on how VLOOKUP with approximate match works if you're interested. so your G4 formula should be:
=VLOOKUP(E3;B2:C15;2;0)

so everything beginning with KSO is 13? otherwise, how does the formula know which one to pick? you can still use the same formula. the asterisk you input in F25 serves as a wildcard:
=VLOOKUP(F25;$C$25:$D$37;2;0)
if you simply put in KSO in F25, then:
=VLOOKUP(F25&"*";$C$25:$D$37;2;0)

Originally Posted by benishiryo

hi there. do add a 0 or FALSE at the end of your VLOOKUP formula to find an exact match. you're playing with fire to use approximate match (without the 0 or FALSE). you can do a google search on how VLOOKUP with approximate match works if you're interested. so your G4 formula should be:
=VLOOKUP(E3;B2:C15;2;0)

so everything beginning with KSO is 13? otherwise, how does the formula know which one to pick? you can still use the same formula. the asterisk you input in F25 serves as a wildcard:
=VLOOKUP(F25;$C$25:$D$37;2;0)
if you simply put in KSO in F25, then:
=VLOOKUP(F25&"*";$C$25:$D$37;2;0)

Please Login or Register  to view this content.

This option is perfect for me. but if my KSO have diferent value and i want take the bigest. ?

it will not be a VLOOKUP, but an array formula then. the asterisk will be useless in this case. if it's without the asterisk, try:
=MAX(IF(LEFT(C25:C37,LEN(F25))=F25,D25:D37))

if it's with the asterisk, the formula needs to remove it:
=MAX(IF(LEFT(C25:C37,LEN(F25)-1)=LEFT(F25,LEN(F25)-1),D25:D37))

if you don't know whether or not there will be an asterisk:
=MAX(IF(LEFT(C25:C37,LEN(F25)-IF(ISNUMBER(FIND("*",F25)),1,0))=LEFT(F25,LEN(F25)-IF(ISNUMBER(FIND("*",F25)),1,0)),D25:D37))

all these 3 formulas...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.