Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Data, Find 2 Adjacent Rows, Return Value in 1 Row
Hello.
I used Excel to import a Text file from an Oracle dbase. In rows are expenses by dept and in columns are $ by year. I specified the original data as Fixed Width and created 21 column breaks (at 21 specified widths after MUCH trial & error to not drop negative signs from $ amounts for when I import the new data each month). My Vlookups have worked perfectly for shorter expense names. However, when column breaks forced lengthy expense names to wrap to a new row, the $ amount for that dept and that year stayed on the previous row. I cannot do a VLookup to that first row, because the expense name in that row is repeated many times in the sheet. I need to somehow 'look up' both values in two adjacent cells-rows and then return the value that's in the first of the 2 rows. The worksheet called "Data" (with 3200 rows, 25 columns of data). I have created VLookups in the other sheet called "Report" to pull in various Expense descriptions by year. A B C 2007 2008 (this is row 1) Dept A Expense 1 10 70 Expense 2 20 25 Adjustments to Plan Dept A 27 50 Dept B Expense 3 20 20 Expense 4 12 18 Adjustments to Plan Dept B 9 16 To get the 2008 Expense 4 for Dept B, on my sheet called "Report" I simply create a Vlookup to A1:C15, and indeed the value 18 is returned. How do I create a VLookup to A1:A15 that will 'look up' both Cell A15 AND Cell A16, then reference column # 3 to return the desired value, which is 16? I have spent much time on your wonderful site (and almost always find answers I seek!), but today I haven't had luck. I am hoping you can please help. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Data, Find 2 Adjacent Rows, Return Value in 1 Row
Can you send the file imported to me? Click on my name to get the address.
Can you import it using delimited method? Can you combine lengthy values in the txt file and then improt? Is there a pattern to the breakup into multiple rows? You should try to add the two cells across rows to one cell on the correct row. If you want lookup across rows then you can use A1&A2 as the value to be looked for but the lookup range must have one cell matiching A1&A2... "Mary" wrote: Hello. I used Excel to import a Text file from an Oracle dbase. In rows are expenses by dept and in columns are $ by year. I specified the original data as Fixed Width and created 21 column breaks (at 21 specified widths after MUCH trial & error to not drop negative signs from $ amounts for when I import the new data each month). My Vlookups have worked perfectly for shorter expense names. However, when column breaks forced lengthy expense names to wrap to a new row, the $ amount for that dept and that year stayed on the previous row. I cannot do a VLookup to that first row, because the expense name in that row is repeated many times in the sheet. I need to somehow 'look up' both values in two adjacent cells-rows and then return the value that's in the first of the 2 rows. The worksheet called "Data" (with 3200 rows, 25 columns of data). I have created VLookups in the other sheet called "Report" to pull in various Expense descriptions by year. A B C 2007 2008 (this is row 1) Dept A Expense 1 10 70 Expense 2 20 25 Adjustments to Plan Dept A 27 50 Dept B Expense 3 20 20 Expense 4 12 18 Adjustments to Plan Dept B 9 16 To get the 2008 Expense 4 for Dept B, on my sheet called "Report" I simply create a Vlookup to A1:C15, and indeed the value 18 is returned. How do I create a VLookup to A1:A15 that will 'look up' both Cell A15 AND Cell A16, then reference column # 3 to return the desired value, which is 16? I have spent much time on your wonderful site (and almost always find answers I seek!), but today I haven't had luck. I am hoping you can please help. Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Data, Find 2 Adjacent Rows, Return Value in 1 Row
Thank you for replying. Yes, I will send to you the *.txt file.
I don't believe I can use delimited, because there are some letter strings that are lengthy/can consist of several words, and there are also some numbers--and I found it difficult to get them into a "mostly okay" columnar format. My Vlookups correctly can pick up all of the other longer text strings--but the one in question today is so very long that if I change the width, numbers are cut up. I'm not sure how to combine lengthy values in the *.txt file...maybe you can advise? If you don't mind, I think I will also send you my Excel file (2 sheets) in hopes that will be helpful and maybe better describe my objective. I'll get both items to you right away. Thank you VERY much for replying. "Sheeloo" wrote: Can you send the file imported to me? Click on my name to get the address. Can you import it using delimited method? Can you combine lengthy values in the txt file and then improt? Is there a pattern to the breakup into multiple rows? You should try to add the two cells across rows to one cell on the correct row. If you want lookup across rows then you can use A1&A2 as the value to be looked for but the lookup range must have one cell matiching A1&A2... "Mary" wrote: Hello. I used Excel to import a Text file from an Oracle dbase. In rows are expenses by dept and in columns are $ by year. I specified the original data as Fixed Width and created 21 column breaks (at 21 specified widths after MUCH trial & error to not drop negative signs from $ amounts for when I import the new data each month). My Vlookups have worked perfectly for shorter expense names. However, when column breaks forced lengthy expense names to wrap to a new row, the $ amount for that dept and that year stayed on the previous row. I cannot do a VLookup to that first row, because the expense name in that row is repeated many times in the sheet. I need to somehow 'look up' both values in two adjacent cells-rows and then return the value that's in the first of the 2 rows. The worksheet called "Data" (with 3200 rows, 25 columns of data). I have created VLookups in the other sheet called "Report" to pull in various Expense descriptions by year. A B C 2007 2008 (this is row 1) Dept A Expense 1 10 70 Expense 2 20 25 Adjustments to Plan Dept A 27 50 Dept B Expense 3 20 20 Expense 4 12 18 Adjustments to Plan Dept B 9 16 To get the 2008 Expense 4 for Dept B, on my sheet called "Report" I simply create a Vlookup to A1:C15, and indeed the value 18 is returned. How do I create a VLookup to A1:A15 that will 'look up' both Cell A15 AND Cell A16, then reference column # 3 to return the desired value, which is 16? I have spent much time on your wonderful site (and almost always find answers I seek!), but today I haven't had luck. I am hoping you can please help. Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Data, Find 2 Adjacent Rows, Return Value in 1 Row
Thank you for your help. I did insert a helper column containing concatenate
formula with 'if(isna)' to help it work. "Sheeloo" wrote: Can you send the file imported to me? Click on my name to get the address. Can you import it using delimited method? Can you combine lengthy values in the txt file and then improt? Is there a pattern to the breakup into multiple rows? You should try to add the two cells across rows to one cell on the correct row. If you want lookup across rows then you can use A1&A2 as the value to be looked for but the lookup range must have one cell matiching A1&A2... "Mary" wrote: Hello. I used Excel to import a Text file from an Oracle dbase. In rows are expenses by dept and in columns are $ by year. I specified the original data as Fixed Width and created 21 column breaks (at 21 specified widths after MUCH trial & error to not drop negative signs from $ amounts for when I import the new data each month). My Vlookups have worked perfectly for shorter expense names. However, when column breaks forced lengthy expense names to wrap to a new row, the $ amount for that dept and that year stayed on the previous row. I cannot do a VLookup to that first row, because the expense name in that row is repeated many times in the sheet. I need to somehow 'look up' both values in two adjacent cells-rows and then return the value that's in the first of the 2 rows. The worksheet called "Data" (with 3200 rows, 25 columns of data). I have created VLookups in the other sheet called "Report" to pull in various Expense descriptions by year. A B C 2007 2008 (this is row 1) Dept A Expense 1 10 70 Expense 2 20 25 Adjustments to Plan Dept A 27 50 Dept B Expense 3 20 20 Expense 4 12 18 Adjustments to Plan Dept B 9 16 To get the 2008 Expense 4 for Dept B, on my sheet called "Report" I simply create a Vlookup to A1:C15, and indeed the value 18 is returned. How do I create a VLookup to A1:A15 that will 'look up' both Cell A15 AND Cell A16, then reference column # 3 to return the desired value, which is 16? I have spent much time on your wonderful site (and almost always find answers I seek!), but today I haven't had luck. I am hoping you can please help. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find lowest number and return it's adjacent cell | Excel Worksheet Functions | |||
find and return adjacent value (redux)! | Excel Discussion (Misc queries) | |||
find and return adjacent value | Excel Discussion (Misc queries) | |||
how do i use VLOOKUP to find text and return a number? | Excel Discussion (Misc queries) | |||
find data in adjacent cell | Excel Discussion (Misc queries) |