Multiple lookup values returning a single result from another worksheet

[jdennis1]

I would like to know if there is a way for me to look for 2 separate values in one worksheet and then return a result from an entirely different worksheet? For instance I need to look for B1 & B15 in my main worksheet.......and in another worksheet find these same 2 values but return a different result from that second worksheet into D16 of my main worksheet. Thanks

MAIN WORKSHEET
B1=Site name
B15=Site Location
D16=Site Type (I do not have Site Type in my main worksheet, this is what I am looking for)

SECONDARY WORKSHEET

COLUMN A-has site names
COLUMN C-has site locations
COLUMN CE-has site type (I need to return this result into D16 in my main worksheet)

 

[ztodd]

Yes, you can do a vlookup and refer to another worksheet. It sound like you need to do the lookup on two columns to make a unique combination- is that right? In other words, B1 (site name) by itself could refer to multiple rows... B15 (site location) by itself could refer to multiple rows... so you have to look for both of them to make a unique combination?

 

[cyrilbrd]

Given in Secondary:

site name		site location	site type
ruth		here	X1
ruth		there	X2
ruth		near	X3
ralph		here	X4
ralph		there	X5
ralph		near	X6

<!--StartFragment--> <colgroup><col width="65" span="4" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

set Named ranges Site_name for A2:A7, Site_Loc for B2:B7 and Site_type for CE2:CE7

Formula in D16 main is =INDEX(Site_type,MATCH(1,(Site_Name=main_jdennis1!B1)*(Site_Loc=main_jdennis1!B15),0)) Ctrl + Shift = Enter not just Enter on a PC or Command + Return on a MAC.
Adjust ranges and tab name to fit your model
Would that work for you?

 

[jdennis1]

Thanks cyrilbrd. I tried the formula and this is the error I received. Any ideas?



-- removed inline image ---