Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 190
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,805
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 190
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 190
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
find lowest number and return it's adjacent cell Erik Excel Worksheet Functions 7 October 2nd 07 02:37 PM
find and return adjacent value (redux)! Todd Lietha Excel Discussion (Misc queries) 3 September 26th 07 01:59 AM
find and return adjacent value Todd Lietha Excel Discussion (Misc queries) 2 September 25th 07 01:26 AM
how do i use VLOOKUP to find text and return a number? roza_j2002 Excel Discussion (Misc queries) 8 August 14th 06 11:59 PM
find data in adjacent cell Leon Jaeggi Excel Discussion (Misc queries) 2 May 27th 06 01:36 PM


All times are GMT +1. The time now is 10:29 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"