Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
Whats 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 dont 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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
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 "Joe" wrote: Whats 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 dont 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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
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:D$65,5),0)) As it is an array formula, commit with Shift+Ctrl+Enter. HTH Kostis Vezerides On Mar 28, 2:59 pm, Joe wrote: 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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
the B3 contains only 5 characthers, it is the Array which contains loads and
only need the first 5. Would the formula still work? "Kevin B" wrote: 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 "Joe" wrote: Whats 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 dont 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |