Match two criterias and give a third colums result

[jd64361]

Hi everyone:
I have a three column I need a formula that matches decription and
CPT and if 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,

 

[Fred Smith]

The most common way to do this is to create a helper column which
concatenates Description and CPT.

Then you can use Vlookup on this results to get the Category.

Regards,
Fred.

 

[jd64361]

Anyone else have a beter idea without combining cell?????

 

[Niek Otten]

What would the criteria be for "better"?

 

[jd64361]

What would the criteria be for "better"?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel






- Show quoted text -

Better means how about a Match to criteria and give the result of the
third column where column one and column two matches!!!
thank you!!!

Pantera

 

[jd64361]

What would the criteria be for "better"?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel






- Show quoted text -

A index Match formula where the criteria from F5 is found in column
description and criteria two from E5 is found in column CPT giving the
result whe both criterias are match from column catergory!!!
thank you,

 

[jd64361]

What would the criteria be for "better"?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel






- Show quoted text -

ANSWER FOUND THANK YOU!!!!!!!!!!!!

 

[T. Valko]

Try this array formula** :

E2 = lookup description
F2 = lookup CPT

=INDEX(C2:C7,MATCH(1,(A2:A7=E2)*(B2:B7=F2),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)