Creating conditinal functions/formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a away to create a funciton that will return this example: the col G value for the logical If col b value = Albany and the col d value = 1999? I'm looking to return a value form an entire column when the values in both of two other columns match specific values

Any help or insight would be greatly appreciated

Happy Holidays

Karen S
 
A little confusing but thy
=sumproduct((b2:b200="Albany")*(d2;D200=1999)*G2:G200)

--
Don Guillett
SalesAid Software
(e-mail address removed)
Karen S said:
Is there a away to create a funciton that will return this example: the
Click to expand...
col G value for the logical If col b value = Albany and the col d value =
1999? I'm looking to return a value form an entire column when the values
in both of two other columns match specific values.
 
Put in say, H2: =IF(AND(B2="ALBANY",D2=1999),G2,"")

There's no case sensitivity for the text match in B2.

--
hth
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
--------------------------------------------------------
Karen S said:
Is there a away to create a funciton that will return this example: the
Click to expand...
col G value for the logical If col b value = Albany and the col d value =
1999? I'm looking to return a value form an entire column when the values
in both of two other columns match specific values.
 
On 2nd thought, perhaps my suggestion
might give what you're after, afterall.. <g>

viz.:

Put in say, H2: =IF(AND(B2="ALBANY",D2=1999),G2,"")

Copy the formula in H2 down col H
as many rows as you have data in the other cols

(Don's sumproduct formula assumes you have numbers in col G and
that you want the total of col G where col B = "Albany" & col D = 1999)
 
Don and Max-

Thank you for both of your input.

Unfortunately, I don't think either of your suggestions will give me what I need. On Don's suggestion, I'm not looking to sum any values, but thank you.

On Max's solution, I'm not looking on just one row for my result. I want to be able to scan two entire columns looking for two provided specific values (ie; "Albany" in one column and "1999" in the other column), one from each column. I want to result with the value in a third column from the row where the two given values match the given criteria in the first two columns.

I think it's going to need a combination of IF, LOOKUP (one or all of them), and AND and maybe MATCH. It needs to use arrays. If the following worked this would be something like what I need:

=IF(VLOOKUP(AND((a1:a50)="Albany"),((b1:b50)="1999"))),c1:c50, "unavailable")

Any other thoughts?

Again, thank you.

Karen S
 
...
......
On Max's solution, I'm not looking on just one row for my result. I want
Click to expand...
to be able to scan two entire columns looking for two provided specific
values (ie; "Albany" in one column and "1999" in the other column), one from
each column. I want to result with the value in a third column from the row
where the two given values match the given criteria in the first two
columns.
I think it's going to need a combination of IF, LOOKUP (one or all of
Click to expand...
them), and AND and maybe MATCH. It needs to use arrays. If the following
worked this would be something like what I need:
=IF(VLOOKUP(AND((a1:a50)="Albany"),((b1:b50)="1999"))),c1:c50,
Click to expand...
"unavailable")

Still think my earlier suggestion should have worked,
albeit you seemed to have changed your specs quite a bit
going by your latest description above:

ok, my understanding of your latest specs:

Check where col A = "Albany" and col B = 1999
Where it is so, return the corresponding value in col C
Otherwise, return "unavailable"

To effect the above, try this:

a. Put in say, D2:
=IF(AND(A2="ALBANY",B2=1999),C2,"unavailable")

[note the absence of quotes "..." for the numerics in col B]

b. Copy the formula in D2 down col D
as many rows as you have data in the cols A, B & C

Col D should return what you're after
 
Hi Max

I appreciate your efforts. I haven't changed what I'm looking for. It's difficult conveying one persons thoughts to another in a manner which anyone would understand, especially with each and every person's sets of assumptions that always exist

Let me try stating this another way

Provide the value in col C where the values on the same row in col A equals Albany and in col B equals 1999

The function/formula that I'm trying ot create is looking for a value on a different worksheet tab within the same workbook. On the other worksheet, locate the row where the value in col A equals Albany and the value in col. B equals 1999. Return the value that is in col C on that row. This value is being returned to the original worksheet tab within the same workbook on a row that will have no relation to the row where the required condition (Albany & 1999) exists

I'm pretty sure I need to include an array in my function

If anyone is still game to help with this, it's very much appreciated

Thank you and Happy New Year

Karen

----- Max wrote: ----

..
.....
On Max's solution, I'm not looking on just one row for my result. I wan
Click to expand...
to be able to scan two entire columns looking for two provided specifi
values (ie; "Albany" in one column and "1999" in the other column), one fro
each column. I want to result with the value in a third column from the ro
where the two given values match the given criteria in the first tw
columnsthem), and AND and maybe MATCH. It needs to use arrays. If the followin
worked this would be something like what I need"unavailable"

Still think my earlier suggestion should have worked
albeit you seemed to have changed your specs quite a bi
going by your latest description above

ok, my understanding of your latest specs

Check where col A = "Albany" and col B = 199
Where it is so, return the corresponding value in col
Otherwise, return "unavailable

To effect the above, try this

a. Put in say, D2
=IF(AND(A2="ALBANY",B2=1999),C2,"unavailable"

[note the absence of quotes "..." for the numerics in col B

b. Copy the formula in D2 down col
as many rows as you have data in the cols A, B &

Col D should return what you're afte

-
ht
Ma
 
Karen S said:
Let me try stating this another way.

Provide the value in col C where the values on the same row in
col A equals Albany and in col B equals 1999.
Click to expand...
....

Try the array formula

=INDEX(foo!C2:C101,MATCH("Albany#1999",foo!A2:A101&"#"&foo!B2:B101,0))

Array formulas require that you hold down [Ctrl] and [Shift] keys before
pressing the [Enter] key.
 
hi, just one "last" attempt from me before I call it quits here <g>

Following your further clarification below:
.... looking for a value on a different worksheet tab
within the same workbook
Click to expand...

Assuming you want to do the check on tab "Sheet2"
where the data starts in row2 down

and your "original worksheet tab" is "Sheet1"

a. Try say, in D2 of Sheet1 :
=IF(AND(Sheet2!A2="ALBANY",Sheet2!B2=1999),C2,"unavailable")

(Note: The start cell doesn't have to be D2,
it can be in D3, A10, B4, etc
whatever cell you want in "Sheet1" to be the start cell)

b. Copy the formula in D2 down col D
as many rows as you have data in the cols A, B & C in Sheet2
to be checked

Col D should return what you want

Just amend "Sheet2!" in the IF() formula
to suit the name of the sheet tab being checked

cheers
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
--------------------------------------------------------
Karen S said:
Hi Max-

I appreciate your efforts. I haven't changed what I'm looking for. It's
Click to expand...
difficult conveying one persons thoughts to another in a manner which anyone
would understand, especially with each and every person's sets of
assumptions that always exist.
Let me try stating this another way.

Provide the value in col C where the values on the same row in col A
Click to expand...
equals Albany and in col B equals 1999.
The function/formula that I'm trying ot create is looking for a value on a
Click to expand...
different worksheet tab within the same workbook. On the other worksheet,
locate the row where the value in col A equals Albany and the value in col.
B equals 1999. Return the value that is in col C on that row. This value
is being returned to the original worksheet tab within the same workbook on
a row that will have no relation to the row where the required condition
(Albany & 1999) exists.
I'm pretty sure I need to include an array in my function.

If anyone is still game to help with this, it's very much appreciated.

Thank you and Happy New Year!

Karen S

----- Max wrote: -----

...
......
On Max's solution, I'm not looking on just one row for my result.
Click to expand...
I want
to be able to scan two entire columns looking for two provided specific
values (ie; "Albany" in one column and "1999" in the other column), one from
each column. I want to result with the value in a third column from the row
where the two given values match the given criteria in the first two
columns. of
them), and AND and maybe MATCH. It needs to use arrays. If the following
worked this would be something like what I need:"unavailable")

Still think my earlier suggestion should have worked,
albeit you seemed to have changed your specs quite a bit
going by your latest description above:

ok, my understanding of your latest specs:

Check where col A = "Albany" and col B = 1999
Where it is so, return the corresponding value in col C
Otherwise, return "unavailable"

To effect the above, try this:

a. Put in say, D2:
=IF(AND(A2="ALBANY",B2=1999),C2,"unavailable")

[note the absence of quotes "..." for the numerics in col B]

b. Copy the formula in D2 down col D
as many rows as you have data in the cols A, B & C

Col D should return what you're after
Click to expand...
 
Harlan,

Back after the holidays. Happy New Year!

This formula works great! Thank you.

I want to do something else with this formula. Can you tell me if and how I can now make the "Albany" a lookup and the "1999" a cell reference?

I already have the lookup defined for "Albany" and tried to replace the word in your formula with my lookup, without success. I also tried replacing the "1999" in your formula with the cell reference, and it didn't like that either. Is there something I need to do with the quotation signs and the #?

Any further assistance is very much appreciated.

Karen S
 
Max,

Back after the holidays. Happy New Year to you.

I very much appreciate your efforts. Unfortunately, your formula looks at only one row for the data, and I need it to look in two entire columns for the data match and then return it back to the original cell.

Again, Thank you for your efforts, and your one "last" attempt.

Karen S
 
...
...
I want to do something else with this formula. Can you tell me if and how I
can now make the "Albany" a lookup and the "1999" a cell reference?

I already have the lookup defined for "Albany" and tried to replace the word
in your formula with my lookup, without success. I also tried replacing the
"1999" in your formula with the cell reference, and it didn't like that
either. Is there something I need to do with the quotation signs and the #?
Click to expand...

The formula in question:

=INDEX(foo!C2:C101,MATCH("Albany#1999",foo!A2:A101&"#"&foo!B2:B101,0))

Changing this to

=INDEX(foo!C2:C101,MATCH("Albany#"&X99,foo!A2:A101&"#"&foo!B2:B101,0))

would fail by returning a #VALUE! error if X99 contained 1999 as a number but
Transition Formula Evaluation were enabled. Check that setting - Tools >
Options, click the Transition tab. If you need that setting enabled, then change
the formula immediately above to

=INDEX(foo!C2:C101,MATCH("Albany#"&TEXT(X99,"0"),foo!A2:A101&"#"&foo!B2:B101,0))

If the second formula were returning #N/A, that'd mean there was no match for
Albany and the year entered in X99 in foo!A2:A101 and foo!B2:B101, respectively.

Now for making Albany a lookup result. Try something like

=INDEX(foo!C2:C101,MATCH(VLOOKUP(x,y,n,0)&"#"&TEXT(X99,"0"),
foo!A2:A101&"#"&foo!B2:B101,0))
 
You're welcome, Karen.
Glad you got the solution from Harlan.

cheers
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
---------------------------------------------
Karen S said:
Max,

Back after the holidays. Happy New Year to you.

I very much appreciate your efforts. Unfortunately, your formula looks at
Click to expand...
only one row for the data, and I need it to look in two entire columns for
the data match and then return it back to the original cell.
 
Hi Harlan-

Again, thank you so much for your help.

Below is the formula I am trying, which seems to follow what you suggested, but is coming back with #N/A.

Any further suggestions or insight?

=INDEX(MKT!$G$17:$G$1000,MATCH(LOOKUP($B35,MUS!$B$17:MUS!$B$1000,MUS!$C$17:MUS!$C$1000)&"#"&TEXT($C35,"0"),MKT!$B$17:$B$1000&"#"&MKT!$D$17:$D$1000,0))

The LOOKUP for the city =LOOKUP($B30,MUS!$B$17:MUS!$B$1000,MUS!$C$17:MUS!$C$1000)
works on its own. Is there possibly something I have incorrect in the punctuation?

This has become a personal challenge and I really don't want to give up and devise an altrernative, less efficient solution.

Please let me know what you suggest.

Thank you.

Karen S

----- Harlan Grove wrote: -----

...
...
I want to do something else with this formula. Can you tell me if and how I
can now make the "Albany" a lookup and the "1999" a cell reference?
in your formula with my lookup, without success. I also tried replacing the
"1999" in your formula with the cell reference, and it didn't like that
either. Is there something I need to do with the quotation signs and the #?
Click to expand...

The formula in question:

=INDEX(foo!C2:C101,MATCH("Albany#1999",foo!A2:A101&"#"&foo!B2:B101,0))

Changing this to

=INDEX(foo!C2:C101,MATCH("Albany#"&X99,foo!A2:A101&"#"&foo!B2:B101,0))

would fail by returning a #VALUE! error if X99 contained 1999 as a number but
Transition Formula Evaluation were enabled. Check that setting - Tools >
Options, click the Transition tab. If you need that setting enabled, then change
the formula immediately above to

=INDEX(foo!C2:C101,MATCH("Albany#"&TEXT(X99,"0"),foo!A2:A101&"#"&foo!B2:B101,0))

If the second formula were returning #N/A, that'd mean there was no match for
Albany and the year entered in X99 in foo!A2:A101 and foo!B2:B101, respectively.

Now for making Albany a lookup result. Try something like

=INDEX(foo!C2:C101,MATCH(VLOOKUP(x,y,n,0)&"#"&TEXT(X99,"0"),
foo!A2:A101&"#"&foo!B2:B101,0))
 
Karen S said:
Below is the formula I am trying, which seems to follow what
you suggested, but is coming back with #N/A. ....
[reformatted]
=INDEX(MKT!$G$17:$G$1000,
MATCH(
LOOKUP($B35,MUS!$B$17:MUS!$B$1000,MUS!$C$17:MUS!$C$1000)
&"#"&TEXT($C35,"0"),
MKT!$B$17:$B$1000&"#"&MKT!$D$17:$D$1000,
0
)
)

The LOOKUP for the city =LOOKUP($B30,MUS!$B$17:MUS!$B$1000,
MUS!$C$17:MUS!$C$1000) works on its own. Is there possibly
something I have incorrect in the punctuation?
Click to expand...

You could shorten MUS!$B$17:MUS!$B$1000 to MUS!$B$17:$B$1000. Your syntax is
supported only when the worksheet names are the same on both sides of ':'.

It's possible you have stray trailing spaces or other whitespace characters
in the values in MUS!C17:C1000 or MKT!B17:B1000. Are the entries in
MKT!D17:D1000 numeric values or strings of numerals? If the former, are they
all whole numbers or do some have fractional parts?
 
Hi Karen,

If "all" that you really want to do is find the values that meet your
requirements, try AutoFilter or Advanced Filter Or Pivot Tables.

As a start, AutoFilter will let you "match" by as many fields as you
may ever wish.

One further option would be to write a small macro which would scan
the range and return your desired results.

This macro could allow you to enter different criteria every time you
run it.


Explore and Enjoy

David
 
Back
Top