Vlookup/hlookup without using row or column numbers

[Guest]

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?

 

[Pete_UK]

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

 

[Roger Govier]

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

 

[Guest]

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]

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

 

[Guest]

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]

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