Vlookup/hlookup without using row or column numbers
 

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?

Vlookup/hlookup without using row or column numbers
 

If you made use of named ranges (which can be created from the column
headings with Insert | Name | Create), then the formulae would be the
same even if the columns were moved,

eg = VLOOKUP(source,table,col,0)

Hope this helps.

Pete

On May 4, 5:23 pm, Dawn - KY <Dawn -
wrote:
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?

Vlookup/hlookup without using row or column numbers
 

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?

Vlookup/hlookup without using row or column numbers
 

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?

Vlookup/hlookup without using row or column numbers
 

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?

Vlookup/hlookup without using row or column numbers
 

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?

Vlookup/hlookup without using row or column numbers
 

Thanks for the feed back letting us know you resolved the problem.


--
Regards

Roger Govier


"Dawn - KY" wrote in message
...
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?