ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   match, lookup two column data (https://www.excelbanter.com/excel-worksheet-functions/195804-match-lookup-two-column-data.html)

KUMPFfrog
match, lookup two column data
 
I want to perform a calculation base off criteria from two columns.

ex:
A B
1 apples 222
2 oranges 156
3 apples 789
4 apples 222
5 oranges 789

if all the 'apples' in column 'A' have the same corrisponding value in
column 'B' then multipy the value in 'B' times some number. In this example,
ALL the 'apples' do not have 'B' values that match so it will return "Varies".

Thanks

RagDyeR
match, lookup two column data
 
One way ... one LONG way, with an *array* formula,

With the lookup item in C1 and the number to multiply in C2:

=IF(COUNT(IF((A2:A6=C1)*(B2:B6=VLOOKUP(C1,A2:B6,2, 0)),B2:B6))=COUNTIF(A2:A6,C1),VLOOKUP(C1,A2:B6,2,0 )*C2,"Varies")

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.



--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===



"KUMPFfrog" wrote in message
...
I want to perform a calculation base off criteria from two columns.

ex:
A B
1 apples 222
2 oranges 156
3 apples 789
4 apples 222
5 oranges 789

if all the 'apples' in column 'A' have the same corrisponding value in
column 'B' then multipy the value in 'B' times some number. In this
example,
ALL the 'apples' do not have 'B' values that match so it will return
"Varies".

Thanks



Lars-Åke Aspelin[_2_]
match, lookup two column data
 
On Tue, 22 Jul 2008 08:21:01 -0700, KUMPFfrog
wrote:

I want to perform a calculation base off criteria from two columns.

ex:
A B
1 apples 222
2 oranges 156
3 apples 789
4 apples 222
5 oranges 789

if all the 'apples' in column 'A' have the same corrisponding value in
column 'B' then multipy the value in 'B' times some number. In this example,
ALL the 'apples' do not have 'B' values that match so it will return "Varies".

Thanks


Try this formula in cell C1

=IF(COUNTIF($A$1:$A$10,A1)<SUMPRODUCT(- -($A$1:$A$10=A1),-
-($B$1:$B$10=B1)),"Varies",B1*$Z$1)

Cell Z1 is where "some number" is.

Hope this helps / Lars-Åke

KUMPFfrog
match, lookup two column data
 
thanks Lars-Ã…ke Aspelin,

only one problem. if my criteria changes to 'oranges' then i would need to
change the 'B1' reference to 'B2' and so on. So, if all the corrisponding
'B' values match, then i would need it to find the 'B' value to the first
criteria match in column 'A'.

Hope that makes sense.



"Lars-Ã…ke Aspelin" wrote:

Try this formula in cell C1

=IF(COUNTIF($A$1:$A$10,A1)<SUMPRODUCT(- -($A$1:$A$10=A1),-
-($B$1:$B$10=B1)),"Varies",B1*$Z$1)

Cell Z1 is where "some number" is.

Hope this helps / Lars-Ã…ke

On Tue, 22 Jul 2008 08:21:01 -0700, KUMPFfrog
wrote:

I want to perform a calculation base off criteria from two columns.

ex:
A B
1 apples 222
2 oranges 156
3 apples 789
4 apples 222
5 oranges 789

if all the 'apples' in column 'A' have the same corrisponding value in
column 'B' then multipy the value in 'B' times some number. In this example,
ALL the 'apples' do not have 'B' values that match so it will return "Varies".

Thanks

KUMPFfrog
match, lookup two column data
 
sorry, i should have provided more details.

continuing with the original example given - i have an area of cells that
will sort of summerize data in the worksheet.
for example: In column E i would have the list of fruits available. In
column F is the number or times that fruit shows up in the range. and in
column G i would use the formula that we/you are trying to create. in my
application this is a date, not a number like i used in the example, but
hopefully you get the idea.


"Lars-Ã…ke Aspelin" wrote:

Sorry, I forgot to mention that you should drag down the formula in C1
as far as needed in column C, that is as many rows as there are data
in the columns A and B.

The result will be a column C filled with either "Varies" or the
values (that do not vary) multiplied with the "some number in Z1".


On Tue, 22 Jul 2008 13:56:00 -0700, KUMPFfrog
wrote:

thanks Lars-Ã…ke Aspelin,

only one problem. if my criteria changes to 'oranges' then i would need to
change the 'B1' reference to 'B2' and so on. So, if all the corrisponding
'B' values match, then i would need it to find the 'B' value to the first
criteria match in column 'A'.

Hope that makes sense.



"Lars-Ã…ke Aspelin" wrote:

Try this formula in cell C1

=IF(COUNTIF($A$1:$A$10,A1)<SUMPRODUCT(- -($A$1:$A$10=A1),-
-($B$1:$B$10=B1)),"Varies",B1*$Z$1)

Cell Z1 is where "some number" is.

Hope this helps / Lars-Ã…ke

On Tue, 22 Jul 2008 08:21:01 -0700, KUMPFfrog
wrote:

I want to perform a calculation base off criteria from two columns.

ex:
A B
1 apples 222
2 oranges 156
3 apples 789
4 apples 222
5 oranges 789

if all the 'apples' in column 'A' have the same corrisponding value in
column 'B' then multipy the value in 'B' times some number. In this example,
ALL the 'apples' do not have 'B' values that match so it will return "Varies".

Thanks

RagDyeR
match, lookup two column data
 
You should maybe try my suggested formula!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"KUMPFfrog" wrote in message
...
thanks Lars-Åke Aspelin,

only one problem. if my criteria changes to 'oranges' then i would need
to
change the 'B1' reference to 'B2' and so on. So, if all the corrisponding
'B' values match, then i would need it to find the 'B' value to the first
criteria match in column 'A'.

Hope that makes sense.



"Lars-Åke Aspelin" wrote:

Try this formula in cell C1

=IF(COUNTIF($A$1:$A$10,A1)<SUMPRODUCT(- -($A$1:$A$10=A1),-
-($B$1:$B$10=B1)),"Varies",B1*$Z$1)

Cell Z1 is where "some number" is.

Hope this helps / Lars-Åke

On Tue, 22 Jul 2008 08:21:01 -0700, KUMPFfrog
wrote:

I want to perform a calculation base off criteria from two columns.

ex:
A B
1 apples 222
2 oranges 156
3 apples 789
4 apples 222
5 oranges 789

if all the 'apples' in column 'A' have the same corrisponding value in
column 'B' then multipy the value in 'B' times some number. In this
example,
ALL the 'apples' do not have 'B' values that match so it will return
"Varies".

Thanks

Lars-Åke Aspelin[_2_]
match, lookup two column data
 

No, I am lost.
What happened to columns A and B that you had in your example?

Please give a new example with all relevant input and the expected
output.


On Tue, 22 Jul 2008 14:52:03 -0700, KUMPFfrog
wrote:

sorry, i should have provided more details.

continuing with the original example given - i have an area of cells that
will sort of summerize data in the worksheet.
for example: In column E i would have the list of fruits available. In
column F is the number or times that fruit shows up in the range. and in
column G i would use the formula that we/you are trying to create. in my
application this is a date, not a number like i used in the example, but
hopefully you get the idea.


"Lars-Åke Aspelin" wrote:

Sorry, I forgot to mention that you should drag down the formula in C1
as far as needed in column C, that is as many rows as there are data
in the columns A and B.

The result will be a column C filled with either "Varies" or the
values (that do not vary) multiplied with the "some number in Z1".


On Tue, 22 Jul 2008 13:56:00 -0700, KUMPFfrog
wrote:

thanks Lars-Åke Aspelin,

only one problem. if my criteria changes to 'oranges' then i would need to
change the 'B1' reference to 'B2' and so on. So, if all the corrisponding
'B' values match, then i would need it to find the 'B' value to the first
criteria match in column 'A'.

Hope that makes sense.



"Lars-Åke Aspelin" wrote:

Try this formula in cell C1

=IF(COUNTIF($A$1:$A$10,A1)<SUMPRODUCT(- -($A$1:$A$10=A1),-
-($B$1:$B$10=B1)),"Varies",B1*$Z$1)

Cell Z1 is where "some number" is.

Hope this helps / Lars-Åke

On Tue, 22 Jul 2008 08:21:01 -0700, KUMPFfrog
wrote:

I want to perform a calculation base off criteria from two columns.

ex:
A B
1 apples 222
2 oranges 156
3 apples 789
4 apples 222
5 oranges 789

if all the 'apples' in column 'A' have the same corrisponding value in
column 'B' then multipy the value in 'B' times some number. In this example,
ALL the 'apples' do not have 'B' values that match so it will return "Varies".

Thanks

KUMPFfrog
match, lookup two column data
 
It works perfectly. I actually did try your formula first, but i think in
trying to convert it from my example to my actual application, I mis-keyed
something. Needless to say, it worked when i gave it a second try.

Thanks sooo much to both you and Lars-Ã…ke Aspelin for your help.

"RagDyer" wrote:

You should maybe try my suggested formula!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"KUMPFfrog" wrote in message
...
thanks Lars-Ã…ke Aspelin,

only one problem. if my criteria changes to 'oranges' then i would need
to
change the 'B1' reference to 'B2' and so on. So, if all the corrisponding
'B' values match, then i would need it to find the 'B' value to the first
criteria match in column 'A'.

Hope that makes sense.



"Lars-Ã…ke Aspelin" wrote:

Try this formula in cell C1

=IF(COUNTIF($A$1:$A$10,A1)<SUMPRODUCT(- -($A$1:$A$10=A1),-
-($B$1:$B$10=B1)),"Varies",B1*$Z$1)

Cell Z1 is where "some number" is.

Hope this helps / Lars-Ã…ke

On Tue, 22 Jul 2008 08:21:01 -0700, KUMPFfrog
wrote:

I want to perform a calculation base off criteria from two columns.

ex:
A B
1 apples 222
2 oranges 156
3 apples 789
4 apples 222
5 oranges 789

if all the 'apples' in column 'A' have the same corrisponding value in
column 'B' then multipy the value in 'B' times some number. In this
example,
ALL the 'apples' do not have 'B' values that match so it will return
"Varies".

Thanks

KUMPFfrog
match, lookup two column data
 
Thanks for all your help.
I was able to make the formula from RagDyer work.

Sorry my example confused you.



"Lars-Ã…ke Aspelin" wrote:


No, I am lost.
What happened to columns A and B that you had in your example?

Please give a new example with all relevant input and the expected
output.


On Tue, 22 Jul 2008 14:52:03 -0700, KUMPFfrog
wrote:

sorry, i should have provided more details.

continuing with the original example given - i have an area of cells that
will sort of summerize data in the worksheet.
for example: In column E i would have the list of fruits available. In
column F is the number or times that fruit shows up in the range. and in
column G i would use the formula that we/you are trying to create. in my
application this is a date, not a number like i used in the example, but
hopefully you get the idea.


"Lars-Ã…ke Aspelin" wrote:

Sorry, I forgot to mention that you should drag down the formula in C1
as far as needed in column C, that is as many rows as there are data
in the columns A and B.

The result will be a column C filled with either "Varies" or the
values (that do not vary) multiplied with the "some number in Z1".


On Tue, 22 Jul 2008 13:56:00 -0700, KUMPFfrog
wrote:

thanks Lars-Ã…ke Aspelin,

only one problem. if my criteria changes to 'oranges' then i would need to
change the 'B1' reference to 'B2' and so on. So, if all the corrisponding
'B' values match, then i would need it to find the 'B' value to the first
criteria match in column 'A'.

Hope that makes sense.



"Lars-Ã…ke Aspelin" wrote:

Try this formula in cell C1

=IF(COUNTIF($A$1:$A$10,A1)<SUMPRODUCT(- -($A$1:$A$10=A1),-
-($B$1:$B$10=B1)),"Varies",B1*$Z$1)

Cell Z1 is where "some number" is.

Hope this helps / Lars-Ã…ke

On Tue, 22 Jul 2008 08:21:01 -0700, KUMPFfrog
wrote:

I want to perform a calculation base off criteria from two columns.

ex:
A B
1 apples 222
2 oranges 156
3 apples 789
4 apples 222
5 oranges 789

if all the 'apples' in column 'A' have the same corrisponding value in
column 'B' then multipy the value in 'B' times some number. In this example,
ALL the 'apples' do not have 'B' values that match so it will return "Varies".

Thanks

RagDyeR
match, lookup two column data
 
You're welcome, and we appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"KUMPFfrog" wrote in message
...
It works perfectly. I actually did try your formula first, but i think in
trying to convert it from my example to my actual application, I mis-keyed
something. Needless to say, it worked when i gave it a second try.

Thanks sooo much to both you and Lars-Ã…ke Aspelin for your help.

"RagDyer" wrote:

You should maybe try my suggested formula!
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"KUMPFfrog" wrote in message
...
thanks Lars-Ã…ke Aspelin,

only one problem. if my criteria changes to 'oranges' then i would
need
to
change the 'B1' reference to 'B2' and so on. So, if all the
corrisponding
'B' values match, then i would need it to find the 'B' value to the
first
criteria match in column 'A'.

Hope that makes sense.



"Lars-Ã…ke Aspelin" wrote:

Try this formula in cell C1

=IF(COUNTIF($A$1:$A$10,A1)<SUMPRODUCT(- -($A$1:$A$10=A1),-
-($B$1:$B$10=B1)),"Varies",B1*$Z$1)

Cell Z1 is where "some number" is.

Hope this helps / Lars-Ã…ke

On Tue, 22 Jul 2008 08:21:01 -0700, KUMPFfrog
wrote:

I want to perform a calculation base off criteria from two columns.

ex:
A B
1 apples 222
2 oranges 156
3 apples 789
4 apples 222
5 oranges 789

if all the 'apples' in column 'A' have the same corrisponding value
in
column 'B' then multipy the value in 'B' times some number. In this
example,
ALL the 'apples' do not have 'B' values that match so it will return
"Varies".

Thanks


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

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com