Got it! After sleeping on it all weekend I came back in fresh this morning
and worked out my problem rather quidkly. The match formula should start with
the same column as the Vlookup formula. When I changed the match formula to
MATCH("Corp Adm",'External P&L - Grp Curr'!$C$29:$BJ$29,0) I got exactly what
I was looking for. Thank you for helping me. This will save me a lot of time
in the future.
"Roger Govier" wrote:
Hi
I think that should be
=VLOOKUP("Equity Income",'External P&L - Grp Curr'!$C$29:$BJ$66,
MATCH("Corp Adm",'External P&L - Grp Curr'!$E$29:$BJ$29,0),0)
I would also put Equity Income and Corp Adm in separate cells, then use
those cell locations in the formula.
The formula then remains unchanged if you want to use a different
Company and/or Customer
--
Regards
Roger Govier
"Dawn - KY" wrote in message
...
Thanks, Roger. At least I'm getting closer to solving my problem. I'm
getting
an answer of 0 now rather than N/A or Value. This is the string I am
now:
=VLOOKUP("Equity Income",'External P&L - Grp
Curr'!$C$29:$BJ$66,MATCH("Corp
Adm",$E$29:$BJ$29,0),0)
Equity income is the account
The next part is the range on a different tab
Corp Adm is the business unit for which I need equity income.
The final part is the range where all the buisness units are located.
"Roger Govier" wrote:
Hi Dawn
Assuming Company is in Column A and Customer's data is in columns B
to Z
=VLOOKUP("Company X",$A$2:$Z$1000,MATCH("Customer Z",$A$1:$Z$1,0),0)
Change ranges to suit
--
Regards
Roger Govier
"Dawn - KY" <Dawn - wrote in message
...
I work with large databases with ever changing column and row
headings.
I
need to create reports that use both static column and row headings
and does
not reference row or column numbers. For instance I need company X
sales for
customer Z. This month that may be column C and row 4 but next
month
it could
be colum S and row 15.
Taking the time to re-write Vlookup or Hlookup functions each month
defeats
the purpose and efficiency of creating the formulas and certainly
decreases
efficiency on my part. Does anyone know how to create a function
that
will
look up values based on two static criteria rather than column and
row
number
references?