Vlookup

[Joe]

What’s the best way for this?
I am using a Vlookup. I am looking to return values for the ones attached to
codes like 53500, 535001. My problem is that in the Array Table the this code
comes attached to a name eg 53500Hotel XXX.

I don’t want to split the table array. Is there any smart way to for
instance tell the Vlookup to look for the first 5digits in the first column
of the array table?

Formula so far: =VLOOKUP(B3,'[Introductory Payments Feb new
download.xls]Left to Spend 07-08'!D$7:F$65,3,FALSE)

 

[Kevin B]

Use the LEFT function to capture the first 5 charcters in column B. Using
your formula as an example, the function would look like the following:

=VLOOKUP(LEFT(B3,5),'[Introductory Payments Feb new download.xls]Left to
Spend 07-08'!D$7:F$65,3,FALSE)

 

[vezerid]

If I understood your post correctly then you need the following
*array* formula:

=INDEX('[Introductory Payments Feb new download.xls]Left to Spend
07-08'!F$7:F$65,MATCH(B3,LEFT('[Introductory Payments Feb new
download.xls]Left to Spend 07-08'!D$7$65,5),0))

As it is an array formula, commit with Shift+Ctrl+Enter.

HTH
Kostis Vezerides

 

[Joe]

the B3 contains only 5 characthers, it is the Array which contains loads and
only need the first 5. Would the formula still work?

Use the LEFT function to capture the first 5 charcters in column B. Using
your formula as an example, the function would look like the following:

=VLOOKUP(LEFT(B3,5),'[Introductory Payments Feb new download.xls]Left to
Spend 07-08'!D$7:F$65,3,FALSE)

--
Kevin Backmann

What’s the best way for this?
I am using a Vlookup. I am looking to return values for the ones attached to
codes like 53500, 535001. My problem is that in the Array Table the this code
comes attached to a name eg 53500Hotel XXX.

I don’t want to split the table array. Is there any smart way to for
instance tell the Vlookup to look for the first 5digits in the first column
of the array table?

Formula so far: =VLOOKUP(B3,'[Introductory Payments Feb new
download.xls]Left to Spend 07-08'!D$7:F$65,3,FALSE)