Two way lookup for text from different sheet

[MilenaKanwal]

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. 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:

	A	B	C	D	E	F	G	H
1			Access
2			Accounting Dashboad	Importer Database	EIC	Wefax	TPA	CAD
3	Employee ID	BP71789	View	View	View	Full	View
4	DS52763	Cont	View	View	Full	View	View
5	WG85640			View	Full	View	View
6	RD11328	Cont	Cont		Full	View
7	LF54825	Full	Full	Full	Full	Full	Full
8	PY82135	View	View		Full	View
9	KE45832	View			Full	View
10	QM49328	View		Cont	Full	Cont	Cont
11	PP54235	Full	Cont	Cont	Full	View	Full

<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>
</TBODY>

​

TO TABLE:

	A	B	C	D	E	F	G
1			Employee ID
2			BP71789	KE45832	LI45219	LZ12113	NO73111
3	Access	Accounting Dashboad	View	View
4	CAD
5	EIC	View
6	Importer Database	View
7	SYS 5
8	TEMKY
9	TPA	View	View
10	Wefax	Full	Full
11	WFST Sharepoint

<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>
</TBODY>

 

[LxQ]

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

 

[MilenaKanwal]

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

 

[MilenaKanwal]

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.

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

 

[LxQ]

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)))

 

[MilenaKanwal]

=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

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)))

 

[LxQ]

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)))

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

 

[MilenaKanwal]

You ROCK!! Thank you, thank you, thank you!

Whew... *sigh of relief*