Index Match or HLOOKUP or Array

[cccbzg]

Hi,

I'm trying to write a macro to gather data from inbox, look through a series of columns to see if the the inbox data is listed (top row) and on a hit, access each row below, one by one to do some calculations. I'm not sure how to approach this. So for example, I want to key in Monday, find the Monday column, and go through the 5 rows to get the numbers 3, then 2, then 6, then 4, then 5. Does that make sense? Also, the # of columns and rows would be variable and data can be changed on the fly. Any help would be very much appreciated. Many thanks!!

[TABLE="width: 281"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Monday[/TD]
[TD]Tuesday [/TD]
[TD]Wednesday[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12[/TD]
[TD]11[/TD]
[TD]10[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 888"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Bonnie
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]

 

[lewisgmorris]

SO when the user types Monday they get the data returned like this?

[TABLE="width: 357"]
<tbody>[TR]
[TD="class: xl65, width: 59"]Monday[/TD]
[TD="class: xl65, width: 47, align: right"]3[/TD]
[TD="class: xl65, width: 59, align: right"]2[/TD]
[TD="class: xl65, width: 64, align: right"]6[/TD]
[TD="class: xl65, width: 64, align: right"]4[/TD]
[TD="class: xl65, width: 64, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[jim may]

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L
1	Monday	Tuesday	Wednesday	Friday	Saturday		Select From:	1	2	3	4	5
2	3	6	3	2	3		Monday	3	2	6	4	5
3	2	4	5	4	4
4	6	8	7	6	5
5	4	9	9	8	6
6	5	12	11	10	7
Sheet1

Cell Formulas
Range	Formula
H2	=INDEX($A$2:$E$6,H$1,MATCH($G2,DaysWeek,0))

Named Ranges
Name	Refers To	Cells
DaysWeek	=Sheet1!$A$1:$E$1

In Cell G2 I use Data Validation (Creating a Dropdown of Monday thru Saturday) using List refers to: = DaysWeek
Copy Cell H2 to I2:L2

 

[cccbzg]

Thanks so much. Although I don't think I described this well, I think you have put me on the right track, Jim.

This is actually part of a large macro which does a bunch of things...long story. What I should have explained is that I would like the user to key the day of the week in an inputbox when prompted. The macro should then validate the day from row 1 and then look up the values in col1. When there is a hit on the day, display a message "Monday found" and then cycle through each remaining row displaying to the user in a message box, each value one at a time, until the end of the column. So the user would key "Monday" - receive "Monday found" then the user would get a message box saying 3, then a box displaying 2, then one displaying 6, one displaying 4, then 5. if there was NO HIT on the day entered, display "not found" end exit the routine.

Thanks to all.

 

[jim may]

With your worksheet displaying ONLY the A1:E6 range info as before, run this macro.. (on a copy of your file, first)

Sub Foo()
Dim MyDay As String
Dim MyCol As Long, i As Long
MyDay = Application.InputBox("Enter Day of Week")
If WorksheetFunction.CountIf(Range("A1:E1"), MyDay) Then
MsgBox MyDay & " Found"
Else
MsgBox MyDay & " Not Found"
Exit Sub
End If
MyCol = WorksheetFunction.Match(MyDay, Range("A1:E1"), 0)
For i = 2 To 6
MsgBox Cells(i, MyCol).Value
Next i
End Sub

 

[cccbzg]

That works just great. Thanks so very much! I do have one additional need. Since the # of columns and rows would be variable I would need to account for that possibility. Again, my thanks for your help!