Multiple lookup values returning a single result from another worksheet

jdennis1

New Member
Joined
Sep 19, 2014
Messages
2
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)
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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?
 
Upvote 0
Given in Secondary:
site namesite locationsite type
ruthhereX1
ruththereX2
ruthnearX3
ralphhereX4
ralphthereX5
ralphnearX6

<!--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?
 
Upvote 0
Thanks cyrilbrd. I tried the formula and this is the error I received. Any ideas?



-- removed inline image ---

 
Upvote 0

Forum statistics

Threads
1,214,773
Messages
6,121,479
Members
449,034
Latest member
Raygers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top