V+HLookup?
I was wondering if there's a built in worksheet function that would allow the
lookup of row and column data. For example I have a table like this, that I want to fill in. James Kirk Hockey [cell B2] Soccer I have a data like this that I want to find the answer for: [Other.Sheet] Name Sport Score James Soccer 10 Kirk Hockey 5 James Hockey 10 Kirk Soccer 10 So in cell B2, I want to write a formula that looks in [Other.Sheet]. If "Name" matches, and "Sport" matches, then return "Score". Can this be done in Excel? Thanks in advance. |
V+HLookup?
Debra Dagleish explains how to:
http://www.contextures.com/xlFunctions03.html (for =index(match())) Susan wrote: I was wondering if there's a built in worksheet function that would allow the lookup of row and column data. For example I have a table like this, that I want to fill in. James Kirk Hockey [cell B2] Soccer I have a data like this that I want to find the answer for: [Other.Sheet] Name Sport Score James Soccer 10 Kirk Hockey 5 James Hockey 10 Kirk Soccer 10 So in cell B2, I want to write a formula that looks in [Other.Sheet]. If "Name" matches, and "Sport" matches, then return "Score". Can this be done in Excel? Thanks in advance. -- Dave Peterson |
V+HLookup?
in B2:
=SUMPRODUCT(--(Sheet2!$A$2:$A$5=B$1),--(Sheet2!$B$2:$B$5=$A2),Sheet2!$C$2:$C$5) Sheet2 is the sheet with your data (Other.sheet) Copy across and down in your table HTH "Susan" wrote: I was wondering if there's a built in worksheet function that would allow the lookup of row and column data. For example I have a table like this, that I want to fill in. James Kirk Hockey [cell B2] Soccer I have a data like this that I want to find the answer for: [Other.Sheet] Name Sport Score James Soccer 10 Kirk Hockey 5 James Hockey 10 Kirk Soccer 10 So in cell B2, I want to write a formula that looks in [Other.Sheet]. If "Name" matches, and "Sport" matches, then return "Score". Can this be done in Excel? Thanks in advance. |
V+HLookup?
Assume your data in Sheet 1
"Sport", "Name" and "Score" are define name ranges In sheet 2 A2: holds Hockey A3: holds Soccer B1: holds James C1: holds Kirk B2: =SUMPRODUCT(--(Sport=Sheet2!$A2),--(Name=Sheet2!B$1),Score) copy accross and down "Susan" wrote: I was wondering if there's a built in worksheet function that would allow the lookup of row and column data. For example I have a table like this, that I want to fill in. James Kirk Hockey [cell B2] Soccer I have a data like this that I want to find the answer for: [Other.Sheet] Name Sport Score James Soccer 10 Kirk Hockey 5 James Hockey 10 Kirk Soccer 10 So in cell B2, I want to write a formula that looks in [Other.Sheet]. If "Name" matches, and "Sport" matches, then return "Score". Can this be done in Excel? Thanks in advance. |
All times are GMT +1. The time now is 11:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com