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
"ceemo" <[email protected]> wrote in
message news:[email protected]...
|
| 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...o&userid=10650
| View this thread: http://www.excelforum.com/showthread...hreadid=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 wrote:
>
> 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...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=552708

--

Dave Peterson

[ceemo]

top draw thnx