# Would like to use HLOOKUP() instead of using many Range Names

• Hi all,

[ATTACH]n1218810[/ATTACH]

Attached is a cutdown of my worksheet, which keeps a track of investments. There can be only a few, or many investments, depending on cashflow. I am trying to find out how to incorporate HLOOKUP() to use in formulae instead of having to create many Range Names.

Here is an example.
For Investment 4036, in cell B26 I would like to have the formula return the interest for July for that investment by using cell A26.
So far, the only way I have worked out to return the Interest value for July is by using Range Names instead of HLOOKUP().
Is there a way to use HLOOKUP() in this formula?

Each year I have to create a new schedule for the coming year, but it will be a pain having to create Range Names for every investment. I want to create these formulae at the start of the year for all available Ledgers.

Thank you,

Neil

• It would be far easier if you consolidated your data into one normalised table and use that for your lookups. YOu are making the classic mistake of getting muddled up between user-friendly dashboards (aesthetics) and data for data analysis.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Thanks AliGW,

Do you have any URL's or how to look for examples or suggestions?

• It's very simple. Instead of having lots of individual tables, you stack them all on top of each other and add a column for the investment number and one for interest rate in normalised (one record per data item) format:

Excel 2016 (Windows) 32 bit [TABLE="class: head"]
[TR="bgcolor: #888888"]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]1[/SIZE]

[/TD]

[SIZE=10px]Inv. No.[/SIZE]

[/TD]

[SIZE=10px]Interest[/SIZE]

[/TD]

[SIZE=10px]Date[/SIZE]

[/TD]

[SIZE=10px]Investment[/SIZE]

[/TD]

[SIZE=10px]Days[/SIZE]

[/TD]

[SIZE=10px]Interest[/SIZE]

[/TD]

[SIZE=10px]Princ + Int[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]2[/SIZE]

[/TD]

[td]

[SIZE=10px]4036[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]1.95%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]July[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,000.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]31[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]72.34[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,072.34[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]3[/SIZE]

[/TD]

[td]

[SIZE=10px]4036[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]1.95%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]August[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,072.34[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]31[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]72.39[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,144.73[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]4[/SIZE]

[/TD]

[td]

[SIZE=10px]4036[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]1.95%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]September[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,144.73[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]30[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]72.44[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,217.17[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]5[/SIZE]

[/TD]

[td]

[SIZE=10px]4036[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]1.95%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]October[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,217.17[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]31[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]72.49[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,289.66[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]6[/SIZE]

[/TD]

[td]

[SIZE=10px]4036[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]1.95%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]November[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,289.66[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]30[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]72.54[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,362.20[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]7[/SIZE]

[/TD]

[td]

[SIZE=10px]4036[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]1.95%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]December[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,362.20[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]31[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]72.59[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,434.79[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]8[/SIZE]

[/TD]

[td]

[SIZE=10px]4036[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]1.95%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]January[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,434.79[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]31[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]72.64[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,507.43[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]9[/SIZE]

[/TD]

[td]

[SIZE=10px]4036[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]1.95%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]February[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,507.43[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]29[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]72.69[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,580.12[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]10[/SIZE]

[/TD]

[td]

[SIZE=10px]4036[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]1.95%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]March[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,580.12[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]31[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]72.74[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,652.86[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]11[/SIZE]

[/TD]

[td]

[SIZE=10px]4036[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]1.95%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]April[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,652.86[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]30[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]72.79[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,725.65[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]12[/SIZE]

[/TD]

[td]

[SIZE=10px]4036[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]1.95%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]May[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,725.65[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]31[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]72.84[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,798.49[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]13[/SIZE]

[/TD]

[td]

[SIZE=10px]4036[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]1.95%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]June[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,798.49[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]30[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]72.89[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]750,871.38[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]14[/SIZE]

[/TD]

[td]

[SIZE=10px]4037[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]2.05%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]July[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]31[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]0.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]15[/SIZE]

[/TD]

[td]

[SIZE=10px]4037[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]2.05%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]August[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]31[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]0.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]16[/SIZE]

[/TD]

[td]

[SIZE=10px]4037[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]2.05%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]September[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]30[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]0.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]17[/SIZE]

[/TD]

[td]

[SIZE=10px]4037[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]2.05%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]October[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]31[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]0.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]18[/SIZE]

[/TD]

[td]

[SIZE=10px]4037[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]2.05%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]November[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]30[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]0.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]19[/SIZE]

[/TD]

[td]

[SIZE=10px]4037[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]2.05%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]December[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]31[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]0.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]20[/SIZE]

[/TD]

[td]

[SIZE=10px]4037[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]2.05%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]January[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]31[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]0.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]21[/SIZE]

[/TD]

[td]

[SIZE=10px]4037[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]2.05%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]February[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]29[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]0.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]22[/SIZE]

[/TD]

[td]

[SIZE=10px]4037[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]2.05%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]March[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]31[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]0.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]23[/SIZE]

[/TD]

[td]

[SIZE=10px]4037[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]2.05%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]April[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]30[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]0.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]24[/SIZE]

[/TD]

[td]

[SIZE=10px]4037[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]2.05%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]May[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]31[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]0.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

[SIZE=10px]25[/SIZE]

[/TD]

[td]

[SIZE=10px]4037[/SIZE]

[/td]

[TD="bgcolor: #FCE4D6"]

[SIZE=10px]2.05%[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]June[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]30[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]0.00[/SIZE]

[/TD]
[TD="bgcolor: #FCE4D6"]

[SIZE=10px]500,000.00[/SIZE]

[/TD]
[/TR]
[/TABLE]
[TABLE="class: grid"]

[tr]

[td]

Sheet: Sheet1

[/td]

[/tr]

[/TABLE]
It's then just a case of using LOOKUP to find the relevant data for each cell in the summary table.

If you have PowerQuery, you can create your summary table automatically from the above. You could also use a pivot table,

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Thanks AliGW,

I'll give that a go throughout today!

While I was initially skeptical about using a data base type of setup, the fact the data is all date-based, the LOOKUP() should work really well.

BTW I have to be careful how much I put into these sheets, because in the industry in which I work, Rural Local Government (small), it is extremely rare to find someone (who will eventually replace me) who has any background in the use of PowerQuery, etc.

Regards,

Neil

• PowerQuery is gaining momentum - we are seeing a huge increase in questions about it on the various forums I frequent. But you don't have to use PQ to create the table above: you just start collating your data in this way instead.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.