Formula to return cell contents based on multiple conditions

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

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)))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Bill" <[email protected]> wrote in message
news:[email protected]...
> 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

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" wrote:

> 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)))
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Bill" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

"Bill" <[email protected]> wrote
> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----