Search and match two colums give third column result
 

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,

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

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

Search and match two colums give third column result
 

On Nov 18, 1:10*pm, 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))

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

Search and match two colums give third column result
 

Works ok for me.

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

wrote:

On Nov 18, 1:10 pm, 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))

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