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)
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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?
 

jdennis1

New Member
Joined
Sep 19, 2014
Messages
2
Thanks cyrilbrd. I tried the formula and this is the error I received. Any ideas?



-- removed inline image ---

 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

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
Top