Formula to return cell contents based on multiple conditions

[Bill]

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 are:

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

 

[Max]

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 are: 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)),"",INDEX(Sheet1!$C:$C,MATCH(TR
IM($A2&B$1),Sheet1!$D:$D,0)))

 

[Bill]

That's a interesting approach, Max. Unfortunately, I'm not able to modify
the data worksheet to include the extra column to concatenate the info in the
columns of interest. I'm only able to access it and need to display the
summary information in the worksheet with the formulas I'm trying to get to
work.

Do you have any idea why the formulas I've written do not work? Or how they
can be modified to produce the desired result?

Thanks again,

Bill

 

[Max]

That's a interesting approach, Max. Unfortunately, I'm not able to modify
the data worksheet to include the extra column to concatenate the info in the
columns of interest. I'm only able to access it and need to display the
summary information in the worksheet with the formulas I'm trying to get to
work.

Do you have any idea why the formulas I've written do not work? Or how they
can be modified to produce the desired result?

We could dispense with the concat col D in Sheet1, Bill
by using a "similar looking" array formula in Sheet2

Assuming the same set-ups in Sheets 1 and 2 earlier
(minus col D in Sheet1)

In Sheet2
-------------
Put instead in B2**:

=IF(ISNA(MATCH(TRIM($A2&B$1),TRIM(Sheet1!$A$2:$A$100 &
Sheet1!$B$2:$B$100),0)),"",INDEX(Sheet1!$C$2:$C$100,MATCH(TRIM($A2&B$1),TRIM
(Sheet1!$A$2:$A$100 & Sheet1!$B$2:$B$100),0)))

Array-enter with CTRL+SHIFT+ENTER
instead of just pressing ENTER

Copy across to C2, fill down

Adapt the ranges to suit: Sheet1!$A$2:$A$100, etc
(but we can't use entire col references now)

**You could also try in B2:

=IF(ISNA(MATCH(TRIM($A2&B$1),TRIM(Name &
Num),0)),"",INDEX(Mon,MATCH(TRIM($A2&B$1),TRIM(Name & Num),0)))

(array-entered, and filled to populate the grid, as above)

This should work as well,
where the names: Name, Num and Mon
are defined ranges created via:
Insert > Name > Create (Top row)
in Sheet1

Think the latter version would be what you tried to do as per your original
post