Offset and Matching

[ceemo]

i want to pull some information from a row where two columns meet my
crieria.

If i were doing this where information in just one column matched i
would use a match() to find the data and then an offset to locate the
data i were after.

Im not sure how to do this with two colums

it almost needs to be like this

offset(A$1,match(and(B:B="AM",C:C="Whole"),0),0)


Please can you help?

 

[Jim Rech]

With problems like this I sometimes cheat and create a temporary new column
with formulas like this: =B1&"XXXX"&C1, copied down, and search against
that. The "XXXX" in the middle is to prevent rows with say "A1" and "D2"
being found when I'm searching on "A" and "1D2".

--
Jim
message |
| i want to pull some information from a row where two columns meet my
| crieria.
|
| If i were doing this where information in just one column matched i
| would use a match() to find the data and then an offset to locate the
| data i were after.
|
| Im not sure how to do this with two colums
|
| it almost needs to be like this
|
| offset(A$1,match(and(B:B="AM",C:C="Whole"),0),0)
|
|
| Please can you help?
|
|
| --
| ceemo
| ------------------------------------------------------------------------
| ceemo's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=10650
| View this thread: http://www.excelforum.com/showthread.php?threadid=552708
|

 

[Dave Peterson]

Another way...

=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't use the whole column.

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

 

[ceemo]

top draw thnx