Search on two criteria and return another value

[scoobadave71]

I am stumped on how to do this one. Have two worksheets
Sheet1
A,B
Asthma, medicare
asthma, medicaid
COPD, commercial
COPD, medicaid

Sheet2
A,B,C
itemnum, asthma, medicare/commercial
itemnum, asthma, medicaid
itemnum, COPD, commercial
itemnum, COPD, medicaid

What I need to do is loop through sheet 1 and find Col A, find the values in sheet2, colb, then find the match of sheet1, colB and sheet2 colC

Been a couple years since I wrote a script so having some brain freeze here.. any help to get started would be appreciated.

 

[MrVillareal]

Hi please provide sample results.

 

[scoobadave71]

Thanks for the quick response. I am just looking to extract the value in column.

Basically on my sheet that comes in, I have name/address information and the 2 columns I specified.

Someone has built a master table that contains the item number. So I am going to create a 3rd sheet and put the name/address information in there, I know how to do that easily. But what I need is to use the two pieces of information to extract the item number and place that on my third sheet.

So sheet1 headings are

FIRST NAME LAST NAME ADD 1 ADD 2 CITY STATE ZIP MAILING TYPE MAILING CONDITION PRODUCT


My Sheet2 headings are:

Item# Mailing Condition Product

What I need to do is find the mailing condition and product from sheet1, and match it to those two fields in sheet2 and pull the item number.

For Mailing condition on Sheet1, I can have for example Asthma and product could be Medicare, Medicaid or Commercial.

On Sheet2 I have two entries for Mailing Condition Asthma because the product code is either commercial/medicare or medicaid

 

[MrVillareal]

Hi is this what you want?

For Cell C2 you should hold Ctrl and Shift and then Enter. Copy down.
Please let me know if this works as intended.

Excel Workbook
	A	B	C
1	Item#	MailingCondition	Product
2	Item1	asthma	medicare/commercial
3	Item2	asthma	medicaid
4	Item3	COPD	commercial
5	Item4	COPD	medicaid
WithItem#

Excel 2010

Excel Workbook
	A	B	C
1	MailingCondition	Product1	Item#
2	asthma	medicare/commercial	Item1
3	asthma	medicaid	Item2
4	COPD	commercial	Item3
5	COPD	medicaid	Item4
Name Info

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[scoobadave71]

Thanks for your help. I actually needed in VBA because we are automating a process. What I did was break the lines and did a lookup function on this with the Index. What you suggested absolutely put me on that path though.