Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bill
 
Posts: n/a
Default 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 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
  #2   Report Post  
Max
 
Posts: n/a
Default

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



  #3   Report Post  
Bill
 
Posts: n/a
Default

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




  #4   Report Post  
Max
 
Posts: n/a
Default

"Bill" 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$1 00 &
Sheet1!$B$2:$B$100),0)),"",INDEX(Sheet1!$C$2:$C$10 0,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(Nam e & 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 <atyahoo<dotcom
----


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can VLOOKUP return multiple answers based on several identical lo. jddtct Excel Worksheet Functions 3 January 11th 05 08:03 AM
Cell contents vs. Formula contents Sarah Excel Discussion (Misc queries) 3 December 15th 04 07:02 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM
Modify Row & Cell Contents based upon Cells Values bpat1434 Excel Worksheet Functions 0 November 7th 04 04:31 PM
Modify Row & Cell Contents based upon Cells Values bpat1434 Excel Worksheet Functions 1 November 7th 04 01:43 PM


All times are GMT +1. The time now is 11:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"