Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and no results with the =left(a1,4) option :)
Hi I run two macros against two sets of data to get two results Set A contains Question numbers and titles (rows a and B) starting at a1 - Can be format 1.1, 10.1, 12.2B etc Set B contains Question numbers and answers (rows a and B) - Can be format 1.1, 10.1 12.2 (ie NO letters sub options For the MOST part the question numbers are the same, the only place they differ is when there is a sub part... for eg... Set A could have 12.2B listed as an option, whereas SET B has 12.2 listed At this stage of the analysis I dont mind dropping the B, and if the answer is 12.2A or 12.2B or 12.2C i still want to match it agains the singular 12.2. Vlookup works just fine where there are no .A or .B, but not when there are .A or .B Setting the condition to FALSE (approximate match) doesnt help, cause not accurate enough. So, i thought of saying (assume 12.2B is in cell a1. Formule =left(A1,4) which would give me just 12.2 Then I run a vlookup on the 12.2 (having dropped the letter, and find a match, but that doesnt work. In fact if I request =left(a2,4) even from a question that does not have a sub part I get an error. So if I vlooked up 10.1 (say in cell a2) I would get an answer. But if I left(a2,4) and looked up that result I would get no answer. Sounds complicated, but it isnt really, maybe I just didnt explain well... HELP!! :) *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and no results with the =left(a1,4) option :)
You are looking up text against a number. This won't match
use vlookup(1*Left(A1,4), -- Regards, Tom Ogilvy "Darin Kramer" wrote in message ... Hi I run two macros against two sets of data to get two results Set A contains Question numbers and titles (rows a and B) starting at a1 - Can be format 1.1, 10.1, 12.2B etc Set B contains Question numbers and answers (rows a and B) - Can be format 1.1, 10.1 12.2 (ie NO letters sub options For the MOST part the question numbers are the same, the only place they differ is when there is a sub part... for eg... Set A could have 12.2B listed as an option, whereas SET B has 12.2 listed At this stage of the analysis I dont mind dropping the B, and if the answer is 12.2A or 12.2B or 12.2C i still want to match it agains the singular 12.2. Vlookup works just fine where there are no .A or .B, but not when there are .A or .B Setting the condition to FALSE (approximate match) doesnt help, cause not accurate enough. So, i thought of saying (assume 12.2B is in cell a1. Formule =left(A1,4) which would give me just 12.2 Then I run a vlookup on the 12.2 (having dropped the letter, and find a match, but that doesnt work. In fact if I request =left(a2,4) even from a question that does not have a sub part I get an error. So if I vlooked up 10.1 (say in cell a2) I would get an answer. But if I left(a2,4) and looked up that result I would get no answer. Sounds complicated, but it isnt really, maybe I just didnt explain well... HELP!! :) *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and no results with the =left(a1,4) option :)
Thanks so much Tom. Amazing!!! thank you *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why don't my filter results always appear on the bottom left? | Excel Discussion (Misc queries) | |||
No filter results in bottom left hand corner | Excel Discussion (Misc queries) | |||
with Autofilter on, display count results in lower left corner wit | Excel Worksheet Functions | |||
Using VLOOKUP for one option, if this option is not valid than sec | Excel Discussion (Misc queries) | |||
?odd results for =left(F#,2) | Excel Worksheet Functions |