Perhaps one alternative way to try
Supposing this table is in Sheet1, A1:C5
-A- -B- -C-
1 Name Num Mon
2 Dale One Jan
3 Barb One Feb
4 Dale Two Mar
5 Barb Two Apr
Use an empty col D to make a concat field
of the Name and Num:
Put in D2: =TRIM(A2&B2)
Copy down
(It's assumed that col D will evaluate to unique strings only,
i.e. there won't be any duplicates in col D)
In Sheet2
-----------
Listed in B1:C1 a One, Two
Listed in A2 down are the names: Barb, etc
Put in B2:
=INDEX(Sheet1!$C:$C,MATCH(TRIM($A2&B$1),Sheet1!$D: $D,0))
Copy across to C2, fill down to populate the table
For the sample data in Sheet1, you'll get:
-A- -B- -C-
1 One Two
2 Barb Feb Apr
And if you need an error trap to return blanks: "" for any unmatched items
(instead of #NAs), put instead in B2:
=IF(ISNA(MATCH(TRIM($A2&B$1),Sheet1!$D:$D,0)),"",I NDEX(Sheet1!$C:$C,MATCH(TR
IM($A2&B$1),Sheet1!$D:$D,0)))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bill" wrote in message
...
I'm trying to write a formula to display the contents of a cell found
within
a named range based on multiple criteria. I've written similar formulas
in
the past, but this one escapes me. I've narrowed it down to a simple
example, which if I can get this to work, I can apply the knowledge to my
more complex spreadsheet.
Given the following data table with named ranges the same as the column
headings in Row 1:
-A- -B- -C-
1 Name Num Mon
2 Dale One Jan
3 Barb One Feb
4 Dale Two Mar
5 Barb Two Apr
I want to return the cell value of the Mon column that corresponds to a
particular Name and Num, resulting in a grid that *should* look like this:
-A- -B- -C-
1 One Two
2 Barb Feb Apr
The incorrect array formulas I currently have in B2 and C2 a
B2 formula: {=IF((Name=$A2)*(Num=B$1),Mon,"X")}
C2 formulat: {=IF((Name=$A2)*(Num=C$1),Mon,"X")}
I've also tried:
B2 formula: {=IF(AND(Name=$A2,Num=B$1),Mon,"X")}
C2 formula: {=IF(AND(Name=$A2,Num=C$1),Mon,"X")}
When tracing the evaluation of these formulas, everything seems to work
fine
until the final step. It just doesn't seem to want to select an
appropriate
single cell to return from the Mon range. Instead of the appropriate
month
text, it displays the X.
What's the right way to write these formulas?
Thanks in advance,
Bill