Formula Question

[rjacmuto32]

I learned a lot from your dashboard stuff and have been using the hyperlink chart with some adapations. It is awesome.


I have a question about enhacing my dashboard. I'm stuck trying to figure out a good forula to do this without VBA. Is this possible what i'm trying to do, or even if my data is laid out bad, what is a better way. I copied and paste these cells from cels and put the letter and numbers in row A and 1. You can copy the stuff below into your excel workbook and it will appear correct in the cells.


My Data set is from G3 to J7. I'll use a small sample as an example. Rows 3 and 4 above that set has some headings.

All my charts in my board will feed off cells B7-D10. My issue I'm stuff with is what is a good way to populate my data in those cells based on waht I put in cell C6. In this example is says Facilties, so I would want to search my data set and return the 100 and 200 numbers into cells C8-D10. if I change c6 to say Building Services it shuold reutrn the 300 and 400 data.


Is there a good formula to use, like a HLookup or maybe an Index Match to get my data from the left to the right?


Thanks,

--Robert

[pre]

A	B	C	D	E	F	G	H	I	J
2
3						Facilities	Facilities	Building Services	Building Services
4						CR RR	CR FIS	CR RR	CR FIS
5					Name	 100.0 	 200.0 	 300.0 	 400.0
6		Facilities			Size	 110.0 	 210.0 	 310.0 	 410.0
7		CR RR	CR FIS		Windo	 120.0 	 220.0 	 320.0 	 420.0
8	Name
9	Size
10	Windo

[/pre]

 

[Luke M]

Looks like your data got messed up when you posted. Try putting the table of data in between backtick marks (`) (key above the tab key on most keyboards). Makes it look nice like this:

[pre]

Name	Score
a	15
b	10
c	20

[/pre]

 

[rjacmuto32]

now check it out. That should be better. Thanks for the `Tip

 

[Luke M]

How's this? Put this formula into C8, copy to cells C8:D10

=INDEX($G$5:$J$7,ROW($A1),MATCH($C$6,$G$3:$J$3,0)+COLUMN(A$1)-1)


The ROW and COLUMN functions are there to help things automatically adjust position.

 

[rjacmuto32]

Thanks! that does work. 2 Questions from that formula?


1) - Why do you have ROW($A1) in middle and Column(A$1)-1 at the end. How does that affect the formula. Then when I copy it down it goes to Row(A2). Just trying to understand how it works besides just accepting that it works. That way maybe in future applciations I can reference this type of formula again.


2) I know you can do something similar with a HLoopkup but I know from using VLookups that when you use a lot of them the calculations are slower. I've been trying to get away from VLookups on massive files. Is you Index Match Formula above better (quicker) then using a HLookup? I'd rather not use any loopup again.

 

[Luke M]

Sure thing.

1. ROW and COLUMN return an integer representing the corresponding ROW and COLUMN. I most often use it when I need something to increment as a formula is copied (as only relative references change when a formula is copied). So, in the first formula, ROW(A1) will evaluate to 1, then the next formula with ROW(A2) will evaluate to 2, and so on. In the formula, ROW is being used as the row_index arguement, so the formula is being told to look at 1st row in index, then 2nd, then 3rd, etc.


The COLUMN function will either return 1 if referring to A1, or 2 if referring to B1. The MATCH function is giving the first instance of the word looking for (Building Services), so I'm using the COLUMN function to add 1 to the value returned from MATCH if we're in the 2nd (CR FIS) column. Does that help?


2. Technically, this method may be considered on par w/ respect to speed when compared with VLOOKUP and HLOOKUP. Check out this cool article about INDEX for why using INDEX is (usually) a better way to go, though.

http://www.excelhero.com/blog/2011/03/the-imposing-index.html

 

[rjacmuto32]

OH wow that is a really good article.