#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 58
Default Match multiple cells

Hi,
I've got the following list:

A B
4999 005
5000 001
5000 003
5000 004
5000 005

I want Match to both find 5000 in column A and find 005 in column B,
returning 6 in this case.

Is this possible?

greets
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Match multiple cells

Try

=COUNTIF(A1:A5,"5000")+COUNTIF(B1:B5,"005")

Mike


"Gunti" wrote:

Hi,
I've got the following list:

A B
4999 005
5000 001
5000 003
5000 004
5000 005

I want Match to both find 5000 in column A and find 005 in column B,
returning 6 in this case.

Is this possible?

greets

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 58
Default Match multiple cells

The reason i'm using match is because i'm using the value which comes out of
it for offset..

For example i fill in 5000_001

It has to look for 5000 in column a, 001 in column b and give me back value
x in column C


"Mike H" wrote:

Try

=COUNTIF(A1:A5,"5000")+COUNTIF(B1:B5,"005")

Mike


"Gunti" wrote:

Hi,
I've got the following list:

A B
4999 005
5000 001
5000 003
5000 004
5000 005

I want Match to both find 5000 in column A and find 005 in column B,
returning 6 in this case.

Is this possible?

greets

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Match multiple cells

Clearly your MATCH formula doesn't work or you wouldn't be posting here.

My formula returns 6 which is a number that you can use in further
calculations. What do you want to do with that number that you can't?

Mike

"Gunti" wrote:

The reason i'm using match is because i'm using the value which comes out of
it for offset..

For example i fill in 5000_001

It has to look for 5000 in column a, 001 in column b and give me back value
x in column C


"Mike H" wrote:

Try

=COUNTIF(A1:A5,"5000")+COUNTIF(B1:B5,"005")

Mike


"Gunti" wrote:

Hi,
I've got the following list:

A B
4999 005
5000 001
5000 003
5000 004
5000 005

I want Match to both find 5000 in column A and find 005 in column B,
returning 6 in this case.

Is this possible?

greets

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 58
Default Match multiple cells

I probably gave a wrong example, fact is that it's a list of 22000 values.
They are not all named 5000.

I'll give a second example which should point it out.

I fill in 2623 and 005

Excel has to give me a value which i can use in offset so i can get cell the
value from Cell C19 (Water)
A B C
....
17 2621 005
18 2623 004
19 2623 005 Water
20 2623 003


I hope this will make it clear. Thanks for any effort put into this!

Gunti
"Mike H" wrote:

Clearly your MATCH formula doesn't work or you wouldn't be posting here.

My formula returns 6 which is a number that you can use in further
calculations. What do you want to do with that number that you can't?

Mike

"Gunti" wrote:

The reason i'm using match is because i'm using the value which comes out of
it for offset..

For example i fill in 5000_001

It has to look for 5000 in column a, 001 in column b and give me back value
x in column C


"Mike H" wrote:

Try

=COUNTIF(A1:A5,"5000")+COUNTIF(B1:B5,"005")

Mike


"Gunti" wrote:

Hi,
I've got the following list:

A B
4999 005
5000 001
5000 003
5000 004
5000 005

I want Match to both find 5000 in column A and find 005 in column B,
returning 6 in this case.

Is this possible?

greets



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Match multiple cells

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

Gunti wrote:

Hi,
I've got the following list:

A B
4999 005
5000 001
5000 003
5000 004
5000 005

I want Match to both find 5000 in column A and find 005 in column B,
returning 6 in this case.

Is this possible?

greets


--

Dave Peterson
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 58
Default Match multiple cells

That's perfect, props!

Gunti

"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

Gunti wrote:

Hi,
I've got the following list:

A B
4999 005
5000 001
5000 003
5000 004
5000 005

I want Match to both find 5000 in column A and find 005 in column B,
returning 6 in this case.

Is this possible?

greets


--

Dave Peterson

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
Find a Match in Multiple Places & Return Multiple Values Toria Excel Worksheet Functions 3 June 24th 08 09:49 PM
MATCH Multiple Criteria & Return Previous / Penultimate Match Sam via OfficeKB.com Excel Worksheet Functions 27 October 6th 07 01:39 AM
Index & Match functions - multiple criteria and multiple results [email protected] Excel Worksheet Functions 4 May 2nd 07 03:13 AM
SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec TravisB Excel Discussion (Misc queries) 21 March 16th 07 10:49 PM
How do I add multiple values that match multiple conditions? Joel Excel Discussion (Misc queries) 5 April 10th 06 01:32 PM


All times are GMT +1. The time now is 06:18 AM.

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"