Excel formula referencing multiple columns between tables.

I have 2 tables each with 2 columns referenced to find an answer and place it on a 3rd table,
sheet1
tid,cid,product,time
120,453,3b5,8:30
130,453,234,8:32
140,30,23,9:00
150,453,3b5,9:01
160,1278,1,9:07
170,30,3b6,9:09

sheet2
cid,product,ran,pri,productname
30,23,na,no,"box of rocks"
30,24,no,yes,"bigger box of rocks"
31,gam,no,no,"rare item"
30,3b5,na,no,"box of rocks, the original"
453,234,5,8,"barrel of monkeys"
453,3b6,4,8,"barrel of dirty monkeys"
1278,15,no,no,"soap"

on a 3rd page i would like to have the following
sheet3
tid,cid,productname,ran
120,453, ,
130,453,"barrel of monkeys",5
140,30,"box of rocks",na
150,453,"box of rocks, the original",na
160,1278, ,
170,30, ,

I have tried vlookup but to no avail, what formula would I use here to match the data across the pages?
LVL 1
csePixelatedAsked:
Who is Participating?
 
Bill PrewConnect With a Mentor Commented:
You can't use VLOOKUP() with more than one value to match, you have to go to a combination of MATCH() and INDEX(), using array formulas.

Attached is the idea.  Sheet3 is basically the first three columns from Sheet1, and then uses that to do the lookup on Sheet2 of cid and product to find the two desired columns.

Notice that the formulas in those added columns on Sheet3 have { } around them, that indicates an array formula.  Array formulas have to be entered with CONTROL-SHIFT-ENTER when you are done editting the formula, rather than the normal ENTER.

Hope this helps and gives you some ideas.

EE29067900.xlsx


»bp
0
 
csePixelatedAuthor Commented:
Thank you for your swift response, this is precisely what i needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.