vlookup and match with multiple column headers

[cimanaxe]

Hi Everyone,

I am having trouble figuring out a vlookup problem. The sheet I use for work on a daily basis has repeating metrics across the sheet under different headers. For example, let's say there are only two metrics and they are "total $" and "total %". Those will be repeating twice with a "TY" above and an "LY" above. Then, they will be repeating maybe ten times with different headers like "Sales, Cost, Margin, etc." It would be setup like this.

Sales	Cost	Margin	Receipts
TY	LY	TY	LY	TY	LY	TY	LY
Total $	Total %	Total $	Total %	Total $	Total %	Total $	Total %

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

How do I do a vlookup or vlookup and match that will look under the correct top header(sales, cost, etc.)? The other problem is that columns can be added or deleted daily, so the sheet is dynamic, and it's too much time to label as named tables each day. Any help would be greatly appreciated!Thank you!

 

[Hashiru]

Hello Cimanaxe, I think you should be looking for HLookup rather than a VLookup. The layout of Sales, Cost, etc. is horizontal. You can use the Insert Function command of the Function Library group of the Formula tab. This will give you the function syntax and details of the arguments. You can then decide what exactly you want. Hope this help.

 

[cimanaxe]

Hello Cimanaxe, I think you should be looking for HLookup rather than a VLookup. The layout of Sales, Cost, etc. is horizontal. You can use the Insert Function command of the Function Library group of the Formula tab. This will give you the function syntax and details of the arguments. You can then decide what exactly you want. Hope this help.

Hi Hashiru,

Thank you for the response. You are correct on the HLookup, but then how do I specify which column within the range? For example, If I HLookup to "Sales," I then want to be able to select which metric below within the Sales grouping (TY or LY). Thanks!

 

[Hashiru]

Hello Cimanaxe, HLookUp, VLookUp and LookUp requires the data to be set in a specified way for the function to work as desired. Particularly in your case HLookUp requires that the first row contain the Lookup_value in your case "Sales" for it to work, you cannot use the second row and the first row. In that case what I would suggest is for the purpose of HLookup to work as desired you would have to combine Row one and two as single Row in this format SalesTY, SalesLY, CostTY, CostLY etc.. At the end of it you should have twice as much headings as before. You table would then look like the sample below

SalesTY SalesLY CostTY CostLY
Total $ Total % Total $ Total %
5000 10 3000 8

You can then insert the HLookup formula to a particular cell. Say B1 = HLOOKUP("sales",A1:H6,3,FALSE). Another thing because the number of rows of values might change you can make table_Array an Excel table so it can accommodate increase in the number of row.

Here is a link that would help you in understanding the syntax and arguments of HLookUp
HLOOKUP function - Excel - Office.com

Hope this further help.