Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking up a text string
I have an issue where I am trying to lookup certain text strings and return
values corresponding to the strings from a separate worksheet. However, the strings do not match exactly on the worksheets. For example on the sheet with the values to lookup it will list "AAAA-BBBB" but on the data worksheet it will have something like "ZZAAAA-BBBB-YY The text string is there, but using vlookup will not work as they do not match exactly. In addition the lookup function doesn't seem to work as there may be other strings in the data sheet such as "AA-BB" which is returned from the lookup function. Is there a way to find the exact string within other strings as part of the array on the data sheet, or is this not possible? Please help. Thank you, Chaim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking up a text string
you can't do it using the lookup functions, but you might be able to do it
with the index function. I experiment with the following list starting in A1. 123a-b45 123a-g45 a-v123456 using the following formula you can find the row number of the item containing "a-g". =SUMPRODUCT(--ISNUMBER(FIND("a-g",A1:A3)),ROW(A1:A3)) and if you wanted to match this number with an item in column B you could use this formula... =INDEX(B:B,SUMPRODUCT(--ISNUMBER(FIND("a-g",A1:A3)),ROW(A1:A3))) This only works if the string is unique in the array. Other wise it will return a funny number. You could use an if function using =IF(SUMPRODUCT(--ISNUMBER(FIND("a-g",A1:A3)),ROW(A1:A3)/ROW(A1:3))=1,INDEX(B:B,SUMPRODUCT(--ISNUMBER(FIND("a-g",A1:A3)),ROW(A1:A3))),"error message") For instance, if you had this list 123a-b45 123a-g45 a-v123456 123va-basd54 searching for "a-b" would give 5 (1+0+0+4) with the first formula I gave, and searching for "45" would give 6 (1+2+3+0). Both of these searches would return "error message" using the last formula. You have a couple options depending on how you want to distinguish the strings. Using the example you gave, how would you clarify that "AA-BB" is not included in "AAAA-BBBB", but is included in something else like "ZZAA-BB-YY"? Because the exact string is in both. "clubin" wrote: I have an issue where I am trying to lookup certain text strings and return values corresponding to the strings from a separate worksheet. However, the strings do not match exactly on the worksheets. For example on the sheet with the values to lookup it will list "AAAA-BBBB" but on the data worksheet it will have something like "ZZAAAA-BBBB-YY The text string is there, but using vlookup will not work as they do not match exactly. In addition the lookup function doesn't seem to work as there may be other strings in the data sheet such as "AA-BB" which is returned from the lookup function. Is there a way to find the exact string within other strings as part of the array on the data sheet, or is this not possible? Please help. Thank you, Chaim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking up a text string
I didn't know you use the asterisk like that. You can use the VLOOKUP
function then can't you? =VLOOKUP("*c*",B15:C:22,2,FALSE) "Don Guillett" wrote: try using match with wildcard to find the row and then use in an index function. =INDEX(B15:C22,MATCH("*c*",B15:B22,0),2) -- Don Guillett SalesAid Software "clubin" wrote in message ... I have an issue where I am trying to lookup certain text strings and return values corresponding to the strings from a separate worksheet. However, the strings do not match exactly on the worksheets. For example on the sheet with the values to lookup it will list "AAAA-BBBB" but on the data worksheet it will have something like "ZZAAAA-BBBB-YY The text string is there, but using vlookup will not work as they do not match exactly. In addition the lookup function doesn't seem to work as there may be other strings in the data sheet such as "AA-BB" which is returned from the lookup function. Is there a way to find the exact string within other strings as part of the array on the data sheet, or is this not possible? Please help. Thank you, Chaim |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking up a text string
This worked but only if AAAA-BBBB appears once in your list. Otherwise, it will return the sum of each instance that AAAA-BBBB appears. =SUMPRODUCT(--(ISNUMBER(FIND("AAAA-BBBB",C10:C13))),--(D10:D13)) Column C is where your text within text is and D is the value to return. Cheers, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=496637 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking up a text string
A small modification. The only advantage to using this instead of the VLOOKUP is if you have multiple lookup values, you can drag this formula down the list and not have to change the lookup criteria manually for each lookup value. It is also not case sensitive. Use FIND instead of SEARCH if case is important. =SUMPRODUCT(--(ISNUMBER(SEARCH(A1,$E$1:$E$4))),--($F$1:$F$4)) A1 is your lookup value. E is where the value is located and F is your return value if TRUE. Again, this only works if your lookup values only appear once in your range. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=496637 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text string and sum | Excel Worksheet Functions | |||
Remove last character of text string | Excel Worksheet Functions | |||
dynamic cell reference within a text string | Excel Worksheet Functions | |||
Extracting from a text string | Excel Worksheet Functions | |||
Formating a text string? | Excel Discussion (Misc queries) |