VLookup Data, Find 2 Adjacent Rows, Return Value in 1 Row

[Mary]

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.

 

[Sheeloo]

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]

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.

 

[Mary]

Thank you for your help. I did insert a helper column containing concatenate
formula with 'if(isna)' to help it work.