Formula to find cell reference when row/column not known

[Factotum]

Could someone help me with a formula (not VBA) to lookup a value in another sheet and return the cell reference? The catch is that I don't know the row or column where the search value will be found. I've tried Find and Index, but either they don't work or I don't understand them.

I have an organization chart (see Employee Chart table below) that shows our company hierarchy. The other tab is a list of the same names, just in columns so that I can more easily analyze and compare to other sheets.

On the List worksheet, I would like a formula in Column A to look at the Chart worksheet, range A1:Z100, and tell me what cell the Employee ID is in.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Location in Chart worksheet[/TD]
[TD]Employee ID[/TD]
[TD]Employee Name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B4[/TD]
[TD]123456[/TD]
[TD]Bill[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]E4[/TD]
[TD]789101[/TD]
[TD]Ted[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B8[/TD]
[TD]454545[/TD]
[TD]Rufus[/TD]
[/TR]
</tbody>[/TABLE]


Here's an example of the Employee Chart worksheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]ID[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Finance Dept[/TD]
[TD][/TD]
[TD][/TD]
[TD]HR Department[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bill[/TD]
[TD]123456[/TD]
[TD][/TD]
[TD]Ted[/TD]
[TD]789101[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Planning Dept.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Rufus[/TD]
[TD]454545[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks for any suggestions!

 

[njimack]

If you're happy using lots of helper columns, the below will work (copy the formulae in column E as far as column AD

Excel Workbook
	A	B	C	D	E
1	Location	ID	Name		1
2	B4	123456	Bill	4	0
3	E4	789101	Ted	4	0
4	B8	454545	Rufus	8	0
List

 

[kvsrinivasamurthy]

ARRAY formula in A2 then drag down. ARRAY formula should be confirmed with Ctrl+Shift+Enter keys together not with Enter key alone.

=CHAR(64+SMALL(IF(ISNUMBER(FIND($C2,'Employee Chart'!$A$2:$E$8)),COLUMN('Employee Chart'!$A$2:$E$8),""),1))&SMALL(IF(ISNUMBER(FIND($C2,'Employee Chart'!$A$2:$E$8)),ROW('Employee Chart'!$A$2:$E$8),""),1)

This formula works If column lies From A to Z.

 

[Factotum]

Thanks kvsrinivasamurthy! I don't completely understand the formula you gave me, but it works beautifully. There's one additional thing I found that I'm trying to add in, but I can't find the right spot for it. I've been trying to add in an Offset(0,2) to your original formula. Maybe that's not possible, but it would be helpful. Thanks!

 

[Rick Rothstein]

Thanks kvsrinivasamurthy! I don't completely understand the formula you gave me, but it works beautifully. There's one additional thing I found that I'm trying to add in, but I can't find the right spot for it. I've been trying to add in an Offset(0,2) to your original formula. Maybe that's not possible, but it would be helpful. Thanks!

What is being offset... the $C2? If so, try changing both $C2 references to $E2.

 

[kvsrinivasamurthy]

As told by Rick Rothstein I am also not clear what you want to change.
Offset(0,2) is avilable in VBA code not in function.

 

[Aladin Akyurek]

Since ID's are unique and no employee works in more than one department...

In A4 of List control+shift+enter, not just enter:

=ADDRESS(MIN(IF('Employee Chart'!$A$2:$E$8=$B2,ROW('Employee Chart'!$A$2:$E$8))),MIN(IF('Employee Chart'!$A$2:$E$8=$B2,COLUMN('Employee Chart'!$A$2:$E$8))),4)

 

[Factotum]

Rick - I'm not sure I phrased my initial question very well. The employee name will not always be consistent between sources, making it difficult to use Vlookups to other documents. For that reason, I was hoping to find the Employee ID in the Chart and return the location of the Employee Name. Hence the offset to find the location one cell to the right of the Employee ID (my earlier response incorrectly showed a (0,2) offset).

kvsrinivasamurthy - Hopefully the above answers your question also. As to the Offset function, it is an available function: =OFFSET(reference, rows, cols, [height], [width]) I've never really used it and I haven't ever used the height/width arguments.

Aladin - it appears that your solution works just like I need it to.


Thank you all for your help and input - it is very much appreciated!

 

[Aladin Akyurek]

@ Fatso

Glad to help. Thanks for the update.