Search and match two colums give third column result

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

jd64361 RemoveThis @gmail.com wrote:
>
> Hi everyone:
> I have a three column I need a formula that matches decription and
> CPT and ii a match found to give the result from category.
>
> Decription CPT Category
> 1. Exams 99201fp Blue
> 1. vision 99203O Orange
> 1. dental 99202n Black
> 1. Exams 99207p White
> 1. other 99206h Purple
> 1. Exams 99208c Red
>
> Thank you,

--

Dave Peterson

Works ok for me.

You may want to share what you tried and what happened when you tried it.

jd64361.TakeThisOut@gmail.com wrote:
>
> On Nov 18, 1:10 pm, Dave Peterson <peter....TakeThisOut@verizonXSPAM.net> 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))
> >
> > jd64....TakeThisOut@gmail.com wrote:
> >
> > > Hi everyone:
> > > I have a three column I need a formula that matches decription and
> > > CPT and ii a match found to give the result from category.
> >
> > > Decription CPT Category
> > > 1. Exams 99201fp Blue
> > > 1. vision 99203O Orange
> > > 1. dental 99202n Black
> > > 1. Exams 99207p White
> > > 1. other 99206h Purple
> > > 1. Exams 99208c Red
> >
> > > Thank you,
> >
> > --
> >
> > Dave Peterson
>
> Does not work any one else!!!!

--

Dave Peterson