Two way lookup for text from different sheet

M

MilenaKanwal

New Member
Joined
Mar 18, 2014
Messages
12
I have been go crazy trying to figure this out... I hope someone can help!

I have a data table that I need to populate from worksheets submitted from various departments. The challenge is that each department uses differing systems, so the table that I need to assimilate the data into includes all access options available in our organization, and each worksheet submitted only reflects systems and employees valid to their department.


I am able to use INDIRECT(ADDRESS9MATCH(MATCH to pull the data if it is in the same worksheet, but as soon as I adjust the formula to pull from another sheet, it fails.:crash: I have successfully used the following function in C3 to pull from the same sheet. What do I need to do to get it to pull data from another sheet???


INDIRECT(ADDRESS(MATCH(N$3,$C$4:$C$12,0)+3,MATCH($M4,$D$3:$I$3,0)+3))


FROM TABLE:
[TABLE="width: 383"]
<COLGROUP><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 950" width=26><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=4 width=61><TBODY>[TR]
[TD="class: xl69, width: 21, bgcolor: #d9d9d9"] [/TD]
[TD="class: xl69, width: 26, bgcolor: #d9d9d9, align: center"]A[/TD]
[TD="class: xl69, width: 64, bgcolor: #d9d9d9, align: center"]B[/TD]
[TD="class: xl69, width: 78, bgcolor: #d9d9d9, align: center"]C[/TD]
[TD="class: xl69, width: 74, bgcolor: #d9d9d9, align: center"]D[/TD]
[TD="class: xl69, width: 61, bgcolor: #d9d9d9, align: center"]E[/TD]
[TD="class: xl69, width: 61, bgcolor: #d9d9d9, align: center"]F[/TD]
[TD="class: xl69, width: 61, bgcolor: #d9d9d9, align: center"]G[/TD]
[TD="class: xl69, width: 61, bgcolor: #d9d9d9, align: center"]H[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #d9d9d9, align: center"]1[/TD]
[TD="class: xl71, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl72, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl76, bgcolor: #e4dfec, colspan: 6, align: center"]Access[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 21, bgcolor: #d9d9d9, align: center"]2[/TD]
[TD="class: xl73, width: 26, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl74, width: 64, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl79, width: 78, bgcolor: #e4dfec, align: center"]Accounting Dashboad[/TD]
[TD="class: xl79, width: 74, bgcolor: #e4dfec, align: center"]Importer Database[/TD]
[TD="class: xl79, width: 61, bgcolor: #e4dfec, align: center"]EIC[/TD]
[TD="class: xl79, width: 61, bgcolor: #e4dfec, align: center"]Wefax[/TD]
[TD="class: xl79, width: 61, bgcolor: #e4dfec, align: center"]TPA[/TD]
[TD="class: xl79, width: 61, bgcolor: #e4dfec, align: center"]CAD[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 21, bgcolor: #d9d9d9, align: center"]3[/TD]
[TD="class: xl68, bgcolor: #daeef3, align: center"]Employee ID[/TD]
[TD="class: xl75, bgcolor: #daeef3, align: center"]BP71789[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl70, width: 21, bgcolor: #d9d9d9, align: center"]4[/TD]
[TD="class: xl75, bgcolor: #daeef3, align: center"]DS52763[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Cont[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 21, bgcolor: #d9d9d9, align: center"]5[/TD]
[TD="class: xl75, bgcolor: #daeef3, align: center"]WG85640[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 21, bgcolor: #d9d9d9, align: center"]6[/TD]
[TD="class: xl75, bgcolor: #daeef3, align: center"]RD11328[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Cont[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Cont[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl70, width: 21, bgcolor: #d9d9d9, align: center"]7[/TD]
[TD="class: xl75, bgcolor: #daeef3, align: center"]LF54825[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 21, bgcolor: #d9d9d9, align: center"]8[/TD]
[TD="class: xl75, bgcolor: #daeef3, align: center"]PY82135[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl70, width: 21, bgcolor: #d9d9d9, align: center"]9[/TD]
[TD="class: xl75, bgcolor: #daeef3, align: center"]KE45832[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl70, width: 21, bgcolor: #d9d9d9, align: center"]10[/TD]
[TD="class: xl75, bgcolor: #daeef3, align: center"]QM49328[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Cont[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Cont[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Cont[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 21, bgcolor: #d9d9d9, align: center"]11[/TD]
[TD="class: xl75, bgcolor: #daeef3, align: center"]PP54235[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Cont[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Cont[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]Full[/TD]
[/TR]
</TBODY>[/TABLE]



​
TO TABLE:

[TABLE="width: 341"]
<COLGROUP><COL style="TEXT-ALIGN: center; WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="TEXT-ALIGN: center; WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 950" width=26><COL style="TEXT-ALIGN: center; WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="TEXT-ALIGN: center; WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=5 width=61><TBODY>[TR]
[TD="class: xl67, width: 21, bgcolor: #d9d9d9, align: center"] [/TD]
[TD="class: xl67, width: 26, bgcolor: #d9d9d9, align: center"]A[/TD]
[TD="class: xl67, width: 100, bgcolor: #d9d9d9, align: center"]B[/TD]
[TD="class: xl67, width: 61, bgcolor: #d9d9d9, align: center"]C[/TD]
[TD="class: xl67, width: 61, bgcolor: #d9d9d9, align: center"]D[/TD]
[TD="class: xl67, width: 61, bgcolor: #d9d9d9, align: center"]E[/TD]
[TD="class: xl67, width: 61, bgcolor: #d9d9d9, align: center"]F[/TD]
[TD="class: xl67, width: 61, bgcolor: #d9d9d9, align: center"]G[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #d9d9d9, align: center"]1[/TD]
[TD="class: xl78, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl79, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl72, bgcolor: #daeef3, colspan: 5, align: center"]Employee ID[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 21, bgcolor: #d9d9d9, align: center"]2[/TD]
[TD="class: xl80, width: 26, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl81, width: 100, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl75, bgcolor: #daeef3, align: center"]BP71789[/TD]
[TD="class: xl75, bgcolor: #daeef3, align: center"]KE45832[/TD]
[TD="class: xl75, bgcolor: #daeef3, align: center"]LI45219[/TD]
[TD="class: xl75, bgcolor: #daeef3, align: center"]LZ12113[/TD]
[TD="class: xl75, bgcolor: #daeef3, align: center"]NO73111[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 21, bgcolor: #d9d9d9, align: center"]3[/TD]
[TD="class: xl69, bgcolor: #e4dfec, align: center"]Access[/TD]
[TD="class: xl76, width: 100, bgcolor: #e4dfec"]Accounting Dashboad[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 21, bgcolor: #d9d9d9, align: center"]4[/TD]
[TD="class: xl76, width: 100, bgcolor: #e4dfec"]CAD[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 21, bgcolor: #d9d9d9, align: center"]5[/TD]
[TD="class: xl76, width: 100, bgcolor: #e4dfec"]EIC[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 21, bgcolor: #d9d9d9, align: center"]6[/TD]
[TD="class: xl76, width: 100, bgcolor: #e4dfec"]Importer Database[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 21, bgcolor: #d9d9d9, align: center"]7[/TD]
[TD="class: xl76, width: 100, bgcolor: #e4dfec"]SYS 5[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 21, bgcolor: #d9d9d9, align: center"]8[/TD]
[TD="class: xl76, width: 100, bgcolor: #e4dfec"]TEMKY[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 21, bgcolor: #d9d9d9, align: center"]9[/TD]
[TD="class: xl76, width: 100, bgcolor: #e4dfec"]TPA[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"]View[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 21, bgcolor: #d9d9d9, align: center"]10[/TD]
[TD="class: xl76, width: 100, bgcolor: #e4dfec"]Wefax[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"]Full[/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl77, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 21, bgcolor: #d9d9d9, align: center"]11[/TD]
[TD="class: xl76, width: 100, bgcolor: #e4dfec"]WFST Sharepoint[/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]
 
Sort by date Sort by votes
I would open this sheet first, then open the other sheet within the same instance of Excel. (That is if we are talking about another file, otherwise, if it's the same file, different tab, then you have it open already)
Then highlight $C$4:$C$12 in your formula and go to the other page and select the range with your mouse.. and press enter. This will replace $C$4:$C$12 with the range from the other worksheet. If it's from a different sheet (tab), then the address will be something like:
INDIRECT(ADDRESS(MATCH(N$3,Sheet1!$C$4:$C$12...

if it's another file then the address will be
INDIRECT(ADDRESS(MATCH(N$3,'Another file.xlsx'!$C$4:$C$12
 
Upvote 0
Thanks LxQ. I actually did that, but I get an N/A response each time. I may have figured it out though... It looks like I had the MATCH functions transposed. It should be row, THEN column... I had it defining column, then row
 
Upvote 0
BUGGER! Still doesn't work!

=INDIRECT(ADDRESS(MATCH(FH21,'FSO&CFOS'!E1:E19,0),MATCH(FH20,'FSO&CFOS'!1:1,0)))

When I F9 the ADDRESS(MATCH I get: =INDIRECT("$I$17")

The defined cell ($I$17) is correct... but it's not referencing the other sheet. :confused:



MilenaKanwal said:
Thanks LxQ. I actually did that, but I get an N/A response each time. I may have figured it out though... It looks like I had the MATCH functions transposed. It should be row, THEN column... I had it defining column, then row
Click to expand...
 
Upvote 0
I think you have to add the sheet name before the ADDRESS:

=INDIRECT("FSO&CFOS!"&ADDRESS(MATCH(FH21,'FSO&CFOS'!E1:E19,0),MATCH(FH20,'FSO&CFOS'!1:1,0)))
 
Upvote 0
=INDIRECT("FSO&CFOS!"&ADDRESS(MATCH(FH21,'FSO&CFOS'!E1:E19,0),MATCH(FH20,'FSO&CFOS'!1:1,0))) gives me a #REF error
=INDIRECT("FSO&CFOS!", ADDRESS(MATCH(FH21,'FSO&CFOS'!E1:E19,0),MATCH(FH20,'FSO&CFOS'!1:1,0))) gives me a #VALUE error

This thing is going to make me nutty


LxQ said:
I think you have to add the sheet name before the ADDRESS:

=INDIRECT("FSO&CFOS!"&ADDRESS(MATCH(FH21,'FSO&CFOS'!E1:E19,0),MATCH(FH20,'FSO&CFOS'!1:1,0)))
Click to expand...
 
Upvote 0
LxQ said:
I think you have to add the sheet name before the ADDRESS:

=INDIRECT("FSO&CFOS!"&ADDRESS(MATCH(FH21,'FSO&CFOS'!E1:E19,0),MATCH(FH20,'FSO&CFOS'!1:1,0)))
Click to expand...

Corr:
=INDIRECT("'FSO&CFOS'!"&ADDRESS(MATCH(FH21,'FSO&CFOS'!E1:E19,0),MATCH(FH20,'FSO&CFOS'!1:1,0)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
N
  • Question Question
excel macro to split data into multiple worksheets based on conditions worksheet
Replies
4
Views
4K
Fluff
Fluff
H
  • Question Question
Vlookup with multiple inputs?
Replies
4
Views
880
Aladin Akyurek
Aladin Akyurek
H
  • Question Question
Index Match 3 Way Look Up
2
Replies
10
Views
2K
DanteAmor
DanteAmor
S
  • Question Question
pivot table calculated field - by broken out values in a column
Replies
1
Views
672
smakatura
S

Share this page

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