HLOOKUP when multiple columns apply to same lookup value

[usnapoleon]

Hello, please help me out. I am using Office 2013 on Windows 7

I have a spreadsheet of data that encompasses data for a month. It's row-oriented, but each day uses 3 columns. Here's an example

Site A1		May 1 2013
	Occupancy	Revenue	Avg Daily Rate
room type 1	2	1500	750
room type 2	3	2467	822.33
room type 3	7	9873	1410.43

<tbody>
</tbody>

So my actual spreadsheet has a lot more rows that continue downward, this is just a quick example of how the data is laid out.

The spreadsheet has 31 days for the month, so May 2nd would be the next 3 columns to the right, and so on after that. At the very end of the columns, after day #31, there are 3 blank columns I am trying to formulate, representing 'Today'. I have an input area where I can input the day of the month (ex. 5, 12, 27 ... basically any day 1-31) and whatever number I input is considered as 'Today'. So if I input 1, then the data from May 1st (the data above in this example) will populate my 'Today' rows and columns, a mirror image. If I change the input to 2, then it will do the same for May 2nd's data.

My issue is my formula. =HLOOKUP(C$2,Setup!L$946:CZ$1389,2,FALSE)
C$2 is the input area where I pick my day.
The 2 (towards the end, before 'False') changes by 1 as it goes down row to row

So I can get the formulas to work for the data column on the left (the occupancy column), but not the other 2 rows. This is because they all share same lookup value. In the Hlookup table array I tried to distinguish the 3 columns with something like 1a 1b 1c, but the formula didnt like that so I had to use 1 1 1.

Can someone please help me?

 

[skywriter]

It's hard for me to tell exactly what you are looking for by your description, but look at this and see if it gives you any ideas.

Excel Workbook
	A	B	C	D	E	F	G	H
13	Site A1		May 1 2013			May 1 2013
14		Occupancy	Revenue	Avg Daily Rate		2	1500	750
15	room type 1	2	1500	750
16	room type 2	3	2467	822.33
17	room type 3	7	9873	1410.43
Sheet8

 

[usnapoleon]

Hi Skywriter,
Thank you for responding. You went a different direction than I, and I dont think that's necessarily a bad thing. I just want to understand your formula's better because I'm not familiar with Index and I tried to wing-it and didnt do so well.

I just got an HTML maker so let me put a more accurate spreadsheet below so you can see exactly how it's laid out.

Some notes for the image below:

• The cells in row 5 are merged every 3 cells, I just couldnt display that for some reason. So C5-E5 are merged, F5-H5 merged, etc etc.
• C2 is the variable I change at-will. If it has 2, like it does now, then my 'Today' columns CT-CU will be populated with the numbers of columns F-H. Or if C2 is 1, then CT-CU will show data from columns C-E

Excel 2012

	A	B	C	D	E	F	G	H	CT	CU	CV
1		Month:	12
2		Date:	2
3		Year:	2006							copied as values for display only
4				Wednesday			Thursday
5		Date	1			2				Current day Display
6		Rental Revenue
7		Site A1	Occupancy	Revenue	ADR	Occupancy	Revenue	ADR	Occupancy	Revenue	ADR
8		Total Available	286			286			286
9		House Use/Model	0			0
10		Maintenance/Out-of-Order	2			1
11		Total Available Rooms	284			285
12		Rental Pool Available	11			15
13		Occupied Owner/Exchanger	273	-	-	270	-	-
14		Internet Booking (.com billing)	3	547.01	182.34	5	911.43	182.29

<tbody>
</tbody>

Last Year​

I tried to wing your formula, came up with (for row 14, since it's the first row with data in all 3 columns):
CT14: =INDEX(C$8:CQ$451,7,MATCH($C$2,$C$5:$CQ$5,0)-1+COLUMNS($CT13:CT13))
CU14: =INDEX(D$8:CR$451,1,MATCH($C$2,$C$5:$CQ$5,0)-1+COLUMNS($CT$13:CU13))
CV14: =INDEX(E$8:CS$451,1,MATCH($C$2,$C$5:$CQ$5,0)-1+COLUMNS($CT$13:CV13))

And it's working great for column CT. But nothing is displaying in CU and CV. Like I said, it's likely due to learning on-the-spot with Index, and Match and Columns as part of the formula! Nothing like learning while you work!!!!! I've a feeling the Columns part of the formula is screwed up.

Help me out please?

I also have a quick question. Columns CW-CY (not shown) are month-to-date columns that I was going to try to formulate after this. When I first started this thread, I had my spreadsheet above plus 2 data arrays: one that had daily numbers and one that did month-to-date numbers. The month-to-date looks like:
Excel 2012

	L	M	N	O	P	Q
1394	Last Year Hlookup Month-to-Date
1395
1396	1a	1b	1c	2a	2b	2c
1397	286.00	-	-	572.00	-	-
1398	-	-	-	-	-	-
1399	2.00	-	-	3.00	-	-
1400	284.00	-	-	569.00	-	-
1401	11.00	-	-	26.00	-	-
1402	273.00	-	-	543.00	-	-
1403	3.00	547.01	182.34	8.00	1,458.44	364.62

<tbody>
</tbody>

Setup​

• 1a, 1b, 1c just represent the 3 columns associated with 1, I was trying stuff, it wasnt working well.
• You can see that in row 1403, column O, it's combining day 1 and 2's numbers from the daily data...

Anyways, your formula for the 'Today' stuff means I can get ride of the daily array because the main spreadsheet is used instead of the data array. Can your Index work on month-to-day stuff too, so that I can get ride of the month-to-day data array?

 

[usnapoleon]

Actually, I saw some formula errors, and fixed them, so the Today stuff is looking great. I'd love if you could give some attention to my Month-To-Day question. It would be great if I could get rid of excess arrays and have the formula look at the original data.

I could really use some help understanding the last half of the formula though.
=INDEX(C$8:CQ$451,12,MATCH($C$2,$C$5:$CQ$5,0)-1+COLUMNS($CT$13:CV17))

I understand the stuff inbetween the() of MATCH... but:

Why I need Match?
What's the -1 do?
Whats +COLUMNS(...) do for this overall formula? And what's the array range inside COLUMNS() signify? I was able to mess with the row numbers inside it and nothing was affected, but delete it and the formula errors.

 

[skywriter]

I haven't read your posts above, I will when I get a chance.

Regarding the index function it in and of itself is a very simple function.

It actually has two forms the first being simply what range is the information you want to return and what row number and column number is this information in.

=index(array where item you want to find is, what row number, what column number)

I used match to give the formula the column number.

With match you first five match the number you want to look up and it returns a number relating to the position of the item you wanted to match in the area you asked it to look in.

4 9 7

If I asked match to match 7 from the three number above it would return 3 because it is the third number

3
7
0

I i asked match to match 7 in the three numbers above it would return 2 because it's in the second row of the three numbers, so match will look in a column or row your choice.

The value I want to return is in the bold cells, the 1 next to it means look in row 1, I could have left this blank since there is no other choice since the data is only referenced on one row.
=INDEX($B$15:$D$15,1,MATCH($F$13,$C$13,0)-1+COLUMNS($F$13:F13))


The whole match function returns the column to look in for the item I want to return in the index function.
=INDEX($B$15:$D$15,1,MATCH($F$13,$C$13,0)-1+COLUMNS($F$13:F13))

So here I tell it to match the date in F13 in the cell C13 and since there is only one cell reference the return value is 1, i then subtract one making it zero but then add back the columns function which returns the number of a column in a referenced range and as I copied the formula to the right this changed the formula to columns function to Columns($F$13:G13) which now returns 2.
=INDEX($B$15:$D$15,1,MATCH($F$13,$C$13,0)-1+COLUMNS($F$13:F13))

I did it this way just to hopefully give you some ideas as to how to do what you want.

I hope this helps you in some way. I will read your posts above and see if I can be of further assistance.

 

[skywriter]

Whats +COLUMNS(...) do for this overall formula? And what's the array range inside COLUMNS() signify? I was able to mess with the row numbers inside it and nothing was affected, but delete it and the formula errors.

Columns is a function that returns the number of columns in a range.

Example: =Columns(A1) = 1
=Columns(A1:C1) = 3

If I have a formula =Columns($A$1:A1) = 1, but if I copy that formula one cell to the right the formula now becomes = ($A$1:B1) = 2 and if I copy it to the next column to the right it becomes 3. This is a little trick used in formulas to increment a number as the formula is copied.

There is another function called rows that does the same thing, but the number returned is based on the number of the rows in the range used, so when you copy the formula down the rows the number changes.

Since you only had the date once above the area I was indexing I needed a way to change the column number in the index formula as I copied it to the next two columns to the right. I could have simply typed 1 in the first formula, 2 in the second etc. but the reason people build formulas this way is because they have to be copied all over the spread sheet, sometimes hundreds of times, so they write formulas this way. I wasn't aware of exactly how your data was laid out and I suspected your date was in a merged cell, so I was trying to give you some ideas. So I hope this helps you understand the reasoning and hopefully gives you more ideas.

 

[usnapoleon]

Very ingenious with the Match & Column stuff!!!! All that to assign columns a number 1,2,3 while defining the ranges.

You are right, my date was a merged cell, thank you for thinking ahead like that.

One thing I noticed, in the very last part of the formula (in the COLUMNS array), changing the row numbers has zero impact, what matters is purely the range of the columns. Should I care to make any corrections to my row numbers?

I need some help making month-to-date formulas, I think the forum rules say I need to make a new thread?

 

[Xergey]

An inelegant but viable solution

=IF(HLOOKUP([lookup value],[array],1,FALSE)<>0,
INDIRECT(ADDRESS
(CELL("row",INDEX([array with the just the titles you are looking for (1 row worked for me)],MATCH([lookup value],[array with the just the titles you are looking for (1 row worked for me)],0))),
CELL("col",INDEX([array with the just the titles you are looking for (1 row worked for me)],MATCH([lookup value],[array with the just the titles you are looking for (1 row worked for me)],0)))))
&" "&
INDIRECT(ADDRESS
(CELL("row",INDEX([array with the just the titles you are looking for (1 row worked for me)],MATCH([lookup value],[array with the just the titles you are looking for (1 row worked for me)],0))),
CELL("col",INDEX([array with the just the titles you are looking for (1 row worked for me)],MATCH([lookup value],[array with the just the titles you are looking for (1 row worked for me)],0)))+1))
&" "&
INDIRECT(ADDRESS
(CELL("row",INDEX([array with the just the titles you are looking for (1 row worked for me)],MATCH([lookup value],[array with the just the titles you are looking for (1 row worked for me)],0))),
CELL("col",INDEX([array with the just the titles you are looking for (1 row worked for me)],MATCH([lookup value],[array with the just the titles you are looking for (1 row worked for me)],0)))+2))

[continue as required adding 1 to the highlighted text each time, finishing with] ,"")

The equation I used in mine without the notes is:
=IF(HLOOKUP(C$1,$A$51:$ZZ$100,$K4,FALSE)<>0,INDIRECT(ADDRESS(CELL("row",INDEX($A$51:$ZZ$51,MATCH(C$1,$A$51:$ZZ$51,0)))+$K3,CELL("col",INDEX($A$51:$ZZ$51,MATCH(C$1,$A$51:$ZZ$51,0)))))&" "&INDIRECT(ADDRESS(CELL("row",INDEX($A$51:$ZZ$51,MATCH(C$1,$A$51:$ZZ$51,0)))+$K3,CELL("col",INDEX($A$51:$ZZ$51,MATCH(C$1,$A$51:$ZZ$51,0)))+1))&" "&INDIRECT(ADDRESS(CELL("row",INDEX($A$51:$ZZ$51,MATCH(C$1,$A$51:$ZZ$51,0)))+$K3,CELL("col",INDEX($A$51:$ZZ$51,MATCH(C$1,$A$51:$ZZ$51,0)))+2)),"")