Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I LOOKUP text values
Hi,
I would like to lookup text values in a worksheet, but is it possible to lookup a non-exact match with a text value? E.g Lookup values attached to 'Apple' against value 'Apples'. For a lookup or match to work, do they have to be exact, or is there an exception rule i can use that will pick out similar? Basically, I have 2 tables, one table on shape of fruit, containing a row of details on 'Apple' and the other table, colur of fruit, containing a row of details for 'Apples'. i want to position the two rows onto the one row, because i know that 'Apple' and 'Apples' is the same thing, with the intention of collating all details about apples onto one row. But the VLOOKUP will only lookup exact values?? i have about 2000 rows, hence the need to automate this 'matching' process If this can not be done, does anyone have any better ideas? Many thanks in advance |
#3
|
|||
|
|||
You can look for "apples" and it will return "apple" by having your Lookup
Table alphabetized and using the "TRUE" option.......but it don't work in the reverse... Vaya con Dios, Chuck, CABGx3 "Amber C-W" wrote: Hi, I would like to lookup text values in a worksheet, but is it possible to lookup a non-exact match with a text value? E.g Lookup values attached to 'Apple' against value 'Apples'. For a lookup or match to work, do they have to be exact, or is there an exception rule i can use that will pick out similar? Basically, I have 2 tables, one table on shape of fruit, containing a row of details on 'Apple' and the other table, colur of fruit, containing a row of details for 'Apples'. i want to position the two rows onto the one row, because i know that 'Apple' and 'Apples' is the same thing, with the intention of collating all details about apples onto one row. But the VLOOKUP will only lookup exact values?? i have about 2000 rows, hence the need to automate this 'matching' process If this can not be done, does anyone have any better ideas? Many thanks in advance |
#4
|
|||
|
|||
What about if i have e.g.
A T & T Bloggs and i want to match with AT & Bloggs Ltd or ADAM co to match with Adam Ltd co. VLookup i know is very sensitive to spaces and characters. i have a feeling there is no way around because each row is unique and the inconsistancies between the match are unique to the instance. So some rows may match better or closer than others. tricky one i think, but not convinced that its not possible. thanks for your help. Amber C-W "Barb Reinhardt" wrote: Well, if the only difference between the cells is an "s" at the end, you could use something like this =VLOOKUP(A1&"s",Sheet2!A1:B1,2,FALSE) Alternatively, you could add a helper column to parse out the first characters of the word (apples) to match a parsed version of what you are trying to match. "Amber C-W" <Amber wrote in message ... Hi, I would like to lookup text values in a worksheet, but is it possible to lookup a non-exact match with a text value? E.g Lookup values attached to 'Apple' against value 'Apples'. For a lookup or match to work, do they have to be exact, or is there an exception rule i can use that will pick out similar? Basically, I have 2 tables, one table on shape of fruit, containing a row of details on 'Apple' and the other table, colur of fruit, containing a row of details for 'Apples'. i want to position the two rows onto the one row, because i know that 'Apple' and 'Apples' is the same thing, with the intention of collating all details about apples onto one row. But the VLOOKUP will only lookup exact values?? i have about 2000 rows, hence the need to automate this 'matching' process If this can not be done, does anyone have any better ideas? Many thanks in advance |
#5
|
|||
|
|||
You can also use something like this to look up "unknowns".....
ie: match the first 5 characters and then anything less than "z" =IF(A1="","",VLOOKUP(LEFT(A1,5)&"z",H:H,1,TRUE)) Vaya con Dios, Chuck, CABGx3 "CLR" wrote: You can look for "apples" and it will return "apple" by having your Lookup Table alphabetized and using the "TRUE" option.......but it don't work in the reverse... Vaya con Dios, Chuck, CABGx3 "Amber C-W" wrote: Hi, I would like to lookup text values in a worksheet, but is it possible to lookup a non-exact match with a text value? E.g Lookup values attached to 'Apple' against value 'Apples'. For a lookup or match to work, do they have to be exact, or is there an exception rule i can use that will pick out similar? Basically, I have 2 tables, one table on shape of fruit, containing a row of details on 'Apple' and the other table, colur of fruit, containing a row of details for 'Apples'. i want to position the two rows onto the one row, because i know that 'Apple' and 'Apples' is the same thing, with the intention of collating all details about apples onto one row. But the VLOOKUP will only lookup exact values?? i have about 2000 rows, hence the need to automate this 'matching' process If this can not be done, does anyone have any better ideas? Many thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup values in multipul sheets and show value in another sheet | Excel Worksheet Functions | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
how do i detect like text and add corresponding values? | Excel Discussion (Misc queries) | |||
text and values combined in one cel | Excel Discussion (Misc queries) |