| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLLOKUP AND SPACES
I have 2 worksheets in the same workbook where i'm trying to lookup store in
both. In sheet 1 the store is entered normal, in sheet 2 it is preceeded by ' spaces. I tried using trim(sheet2!range)etc but get a #value! error. I messed around with the match function trying to get this to work and could when I used the value in sheet 2 1st, and I had to trim it. when I ran the formula with sheet 1 1st, even with trim on sheet 2 I got the #value error. I know I could trim the column in sheet 2 1st and then it would work, but I can't mess with the format of someone elses worksheet. Thoughts on how to make this work? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLLOKUP AND SPACES
Try this:
Sheet1 A1 = lookup_value: store Sheet2 some cell in column A = __store =VLOOKUP("*"&A1,Sheet2!A1:B5,2,0) Biff "toolman" wrote in message ... I have 2 worksheets in the same workbook where i'm trying to lookup store in both. In sheet 1 the store is entered normal, in sheet 2 it is preceeded by ' spaces. I tried using trim(sheet2!range)etc but get a #value! error. I messed around with the match function trying to get this to work and could when I used the value in sheet 2 1st, and I had to trim it. when I ran the formula with sheet 1 1st, even with trim on sheet 2 I got the #value error. I know I could trim the column in sheet 2 1st and then it would work, but I can't mess with the format of someone elses worksheet. Thoughts on how to make this work? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLLOKUP AND SPACES
Worked perfect. Thanks
"T. Valko" wrote: Try this: Sheet1 A1 = lookup_value: store Sheet2 some cell in column A = __store =VLOOKUP("*"&A1,Sheet2!A1:B5,2,0) Biff "toolman" wrote in message ... I have 2 worksheets in the same workbook where i'm trying to lookup store in both. In sheet 1 the store is entered normal, in sheet 2 it is preceeded by ' spaces. I tried using trim(sheet2!range)etc but get a #value! error. I messed around with the match function trying to get this to work and could when I used the value in sheet 2 1st, and I had to trim it. when I ran the formula with sheet 1 1st, even with trim on sheet 2 I got the #value error. I know I could trim the column in sheet 2 1st and then it would work, but I can't mess with the format of someone elses worksheet. Thoughts on how to make this work? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLLOKUP AND SPACES
You're welcome. Thanks for the feedback!
Biff "toolman" wrote in message ... Worked perfect. Thanks "T. Valko" wrote: Try this: Sheet1 A1 = lookup_value: store Sheet2 some cell in column A = __store =VLOOKUP("*"&A1,Sheet2!A1:B5,2,0) Biff "toolman" wrote in message ... I have 2 worksheets in the same workbook where i'm trying to lookup store in both. In sheet 1 the store is entered normal, in sheet 2 it is preceeded by ' spaces. I tried using trim(sheet2!range)etc but get a #value! error. I messed around with the match function trying to get this to work and could when I used the value in sheet 2 1st, and I had to trim it. when I ran the formula with sheet 1 1st, even with trim on sheet 2 I got the #value error. I know I could trim the column in sheet 2 1st and then it would work, but I can't mess with the format of someone elses worksheet. Thoughts on how to make this work? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vllokup | Excel Discussion (Misc queries) | |||
| VLLOKUP formulas | Excel Discussion (Misc queries) | |||
| VLLOKUP - more than one result?? | Excel Worksheet Functions | |||
| Find last match in vllokup | Excel Discussion (Misc queries) | |||
| Using VLLOKUP | Excel Worksheet Functions | |||
Linear Mode
