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!

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!

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!