Lookup on first five char

**day92** · 01-03-2012, 07:00 PM

Does anyone know why this formula is not working when it gets to line 75? I'm trying to return a value on based on the first five characters.

Thanks!

**jeffreybrown** · 01-03-2012, 07:07 PM

If I understand this right, why not use a Vlookup...

Try this on C75...

=VLOOKUP(LEFT(A75,5)+0,wru_cpt!$A$2:$C$7910,3,0)

...and since you are using 2007 you could use the IFERROR when there is not a match

**day92** · 01-03-2012, 07:11 PM

Yeah that works.

Whats the point of the +0 after the left formula? I dont get that part.

**Special-K** · 01-03-2012, 07:14 PM

You're LEFTing the array but you're not LEFTing the value on Shindel
This cures it

=SUMPRODUCT(--(LEFT(wru_cpt!$A$3:$A$7910,5)=LEFT(A4,5)&""),wru_cpt!$C$3:$C$7910)

and copy column C

Values on Shindel from row 75 are longer than 5 characters
so you're comparing the array with Shindel's original values

Re jeffreybrowns answer: Adding +0 turns the resut into a number rather than a string

**TMS** · 01-03-2012, 07:15 PM

Jeff's formula will give #N/A for a "miss".

Modifying your formula:

Row 3: =SUMPRODUCT(--(LEFT(wru_cpt!$A$3:$A$7910,5)=LEFT(A4,5)&""),wru_cpt!$C$3:$C$7910)

and copy down.

Regards, TMS

**Jack in the UK** · 01-03-2012, 07:20 PM

This should help you bit VBA normal stuff

in cell A1 just exactly this

jackintheuk

Now run this code and your get jacki ONLY - edit to suite, codes pretty basic so hope helps or post back for more help

Please Login or Register  to view this content.

**day92** · 01-03-2012, 07:33 PM

Thanks everyone for your assistance.

Actually I modified Jeff's formula to account for the misses. TMS your formula works but it is picking up extra values for a few of the codes. Take a look at line 47 & 61.

**jeffreybrown** · 01-03-2012, 07:40 PM

@Day92,

Can you confirm what the result for 99213 and 99253 should be?

@TMS,
That is why I also suggested the use of IFERROR. Wouldn't this suffice? It also seems like Vlookup would be a more appropriate choice over Sumproduct. Am I wrong in my thinking?

**day92** · 01-03-2012, 08:04 PM

99213 should be 1.01
99253 should be 2.36

**jeffreybrown** · 01-03-2012, 08:11 PM

Okay, thank you for the confirmation. So the Vlookup should be working for you then, right?

The +0 is just a way to take a number which could be masquerading as text and converts it to a number.

I also did a quick time test between the Sumproduct and Vlookup. The results .969 and .04 respectively.

http://msdn.microsoft.com/en-us/library/aa730921.aspx

**day92** · 01-03-2012, 08:18 PM

Jeff - yes the vlookup worked. I learned something new today. Thanks again!

**jeffreybrown** · 01-03-2012, 08:34 PM

Glad you found the help you needed

**TMS** · 01-04-2012, 03:28 AM

@Jeff: sorry, missed that comment. I agree that VLOOKUP is the better option; I was just thinking "inside the box" and making the original formula work