return a value based on column (date) and row headers (names)
Hi,
I am trying to find an easy way that will return the value depending on the column header and the row header.
I have attached an example spreadsheet to make it a bit clearer.
Any help would be massively appreciated as I have been trying everything for weeks now and I’m still no further.
Thank you
Last edited by NBVC; 02-08-2012 at 07:57 AM.
Try:
=INDEX(B5:K16,MATCH(Q3,A5:A16,0),MATCH(Q2,B3:K3,0))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
NBVC, thank you for your reply.
For that file that was made to post here it works, but in the original file i have to make it work, it's not :-( I don't get it!!
Only small things have changed, like cell number/letters! I don't get what i am doing wrong :-(
Re: return a value based on column (date) and row headers (names)
Incidentes.xlsx
This is the "original file". I had to delete some stuff, so don't mind the other errors.
What do you think it's happening? Thank you
Try again;
Copy across=INDEX($L$4:$V$16,MATCH(C$25,$L$4:$L$16,0),MATCH($A$2,$L$3:$V$3,0))
Click the star icon in left-corner of my post if you find my post userful!
Quang PT
quangphanidico@yahoo.com
PM me: Y!M: quangphanidico
The formula should be:
=INDEX($M$5:$V$16,MATCH(G$4,$L$5:$L$16,0),MATCH($A$2,$L$3:$V$3,0))
Note that the first argument is the range of cells containing the numbers to return
The second argument is the ROW argument, so you need to match ROW to the dates first, then the COLUMN is matched for the names... also MATCH function only allows single column or row references, you can't use multiple rows/columns in that function.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
THANK YOU :-)
It worked :-)
Consider yourself... "star...ed" :-)
I had to change a letter :-) because it was giving me the wrong count.
=INDEX($M$5:$V$16,MATCH(G$4,$L$5:$L$16,0),MATCH($A$2,$L$3:$V$3,0))
=INDEX($M$5:$V$16,MATCH(G$4,$L$5:$L$16,0),MATCH($A$2,$m$3:$V$3,0))
(at the end :-) )
Easy solved. Thank you for the explanation.
QUESTION: In this same last file, how do I get the AVERAGE from de line in question?
Can you help me on that?
Not sure what is the "line in question"?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Sory. My brain was still storming about it :-)
What I want is, It reads the DATE at G4, and then consults the table M5V16 to find the LINE with that DATE and calculates the AVERAGE of that line.
You appear to already have the averages calculated so
=INDEX($W$5:$W$16,MATCH(C25,$L$5:$L$16,0))
Good luck.
You are right. Geeesh :-( I have to rest for a while before I try to make this :-)
Thank you for your patience.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks