Match multiple cells

[Gunti]

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

 

[Mike H]

Try

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

Mike

 

[Gunti]

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]

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]

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

 

[Dave Peterson]

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]

That's perfect, props!

Gunti

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