Match a row and column and retrive a result 1

[YANKRAY]

Excel 2007

I have row headers and column headers for example.

ANDY BILL CHUCK DAN
Jan
Feb
Mar
Apr
May

I am trying to match values from a data set (stored below the row/columns above) to fill the corresponding fields.

Example data would look like this;

Jan Andy 20
Jan Chuck Absent
Mar Bill 20

So I want to match Jan & Andy at the top to the data and the bottom and show 20 under Andy for Jan.

Can anyone help me to do this double match? The data I am retrieving is not always numeric and the column headings are not always dates.

Thanks,
Ray

 

[SkipVought]

Hi,

you can use VLOOKUP and MATCH on the column selector, something like
[tt]
=VLOOKUP(MonLookupValue,lookuprange,MATCH(PersonLookupValue,NamesRange,0),false)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[YANKRAY]

Thanks Skip,

I must be doing something wrong;
I am getting a result, but not what I expected.
The cell below Column "A" Row "1" should show "andy1 not "1". The cell below Column "A" Row "2" should show "andy2" not "2".


A B C
1 1
2 2
3 3
4
5
6
7
8


1 a andy1
3 A bob1
1 c chuck1
2 a andy2
2 b bob2
2 c chuck2

 

[SkipVought]

Sorry, I misread your original question.

However, your response is puzzeling. I thought you wanted to fill in this table..
[tt]
ANDY BILL CHUCK DAN
Jan
Feb
Mar
Apr
May
[/tt]
If that's the case then, here's the drill.

1) I used named ranges for clarity, as follows, for the source table...
[tt]
Mon Person Val
Jan Andy 20
Jan Chuck Absent
Mar Bill 20
[/tt]
Then, assuming that your fillin table is in A1, the formula in B2 to copy across and down is...
[tt]
=IF(ISNA(MATCH($A2&B$1,Mon&Person,0)),"",INDEX(Val,MATCH($A2&B$1,Mon&Person,0),1))
[/tt]
entered as an ARRAY FORMULA (ctr+SHIFT+ENTER)

My results...
[tt]
ANDY BILL CHUCK DAN
Jan 20 Absent
Feb
Mar 20
Apr
May
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[YANKRAY]

Skip,

That did it. Thanks.

Sorry about my poor discription of the problem.

Thanks again,

Ray

 

[SkipVought]

FYI,

It becomes a problem in many cases, when you mix NUMERIC & TEXT data in the same column.

Furthermore, you mentions DATES and I see NONE in your data. I did, however see months, which are not dates, but TEXT.

It seems that you have already summarized some of your data in the source table you posted. What data more often looks like is...
[tt]
Dte Person Val
1/5/2009 Andy 5
1/15/2009 Andy 12
1/20/2009 Andy 3
3/4/2009 Bill 8
3/4/2009 Bill 12
[/tt]
The values for Andy and Bill sum to 20 in the respecitve months. Notice that Chuck is absent. So is Dan, Sam and Nan, along with a host of others. You normally do not show no data (intentional double negatives)

For this kind of data you can get a similar result by month, using the SUMPRODUCT function and REAL DATES in the Dte column. I can elucidate if you care to know.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}