Find the data and get the cell address

[mb8marmed]

I am looking for data in a table if it exists, and I use Ctrl + F to manually find the data within the table. However, I am wondering if there is a way to use the combined Excel functions such as Index, Match, & Address (or any other function). The data to look at is in Sheet 1, and the lookup value is located in Sheet 2. Once the data is found in Sheet 1, the cell reference will be documented in Sheet 2, otherwise it will return to "Not Found". I attached a sample of the work using xl2bb for reference. It would also be great if you could provide me with an Excel function and an alternative VBA code.

Thank you in advance.

Find the Data.xlsx
	B	C	D	E	F
1	Items to Find
2	RFI-001	RFI-021	RFI-031	RFI-041	RFI-051
3	RFI-002	RFI-022	RFI-032	RFI-042	RFI-052
4	RFI-003	RFI-023	RFI-033	RFI-043	RFI-053
5	RFI-004	RFI-024	RFI-034	RFI-044	RFI-054
6	RFI-005	RFI-025	RFI-035	RFI-045	RFI-055
7	RFI-006	RFI-026	RFI-036	RFI-046	RFI-056
8	RFI-007	RFI-027	RFI-037	RFI-040	RFI-057
9	RFI-008	RFI-028	RFI-038	RFI-048	RFI-058
10	RFI-009	RFI-029	RFI-039	RFI-049	RFI-059
11	RFI-010	RFI-030	RFI-040	RFI-050	RFI-060
12	RFI-011	RFI-031	RFI-041	RFI-051	RFI-061
13	RFI-012	RFI-032	RFI-042	RFI-052	RFI-062
14	RFI-013	RFI-033	RFI-043	RFI-053	RFI-063
15	RFI-014	RFI-034	RFI-044	RFI-054	RFI-064
16	RFI-015	RFI-035	RFI-045	RFI-055	RFI-065
17	RFI-016	RFI-036	RFI-046	RFI-056	RFI-066
18	RFI-017	RFI-037	RFI-047	RFI-057	RFI-067
19	RFI-018	RFI-038	RFI-048	RFI-058	RFI-068
20	RFI-019	RFI-039	RFI-049	RFI-059	RFI-069
21	RFI-020	RFI-040	RFI-050	RFI-060	RFI-070
Sheet1

Find the Data.xlsx
	B	C	D	E
2	Item	Lookup Value	Cell Ref.	Sheet Tab
3	1	RFI-0001	Not Found	Not Found
4	2	RFI-024	C5	Sheet 1
5	3	RFI-040	D11, E8	Sheet 1
6	4	RFI-068	F19	Sheet 1
7	5	RFI-xxx	Not Found	Not Found
Sheet2

 

[Peter_SSs]

Does this do what you want?

mb8marmed.xlsm
	B	C	D
2	Item	Lookup Value	Cell Ref.
3	1	RFI-0001	Not found
4	2	RFI-024	C5
5	3	RFI-040	E8, D11, C21
6	4	RFI-068	F19
7	5	RFI-xxx	Not found
Sheet2

Cell Formulas
Range	Formula
D3:D7	D3	=LET(r,Sheet1!$B$2:$F$21,a,TEXTJOIN(", ",1,IF(r=C3,ADDRESS(ROW(r),COLUMN(r),4),"")),IF(a="","Not found",a))

 

[mb8marmed]

Does this do what you want?

mb8marmed.xlsm
	B	C	D
2	Item	Lookup Value	Cell Ref.
3	1	RFI-0001	Not found
4	2	RFI-024	C5
5	3	RFI-040	E8, D11, C21
6	4	RFI-068	F19
7	5	RFI-xxx	Not found
Sheet2

Cell Formulas
Range	Formula
D3:D7	D3	=LET(r,Sheet1!$B$2:$F$21,a,TEXTJOIN(", ",1,IF(r=C3,ADDRESS(ROW(r),COLUMN(r),4),"")),IF(a="","Not found",a))

Does this do what you want?

mb8marmed.xlsm
	B	C	D
2	Item	Lookup Value	Cell Ref.
3	1	RFI-0001	Not found
4	2	RFI-024	C5
5	3	RFI-040	E8, D11, C21
6	4	RFI-068	F19
7	5	RFI-xxx	Not found
Sheet2

Cell Formulas
Range	Formula
D3:D7	D3	=LET(r,Sheet1!$B$2:$F$21,a,TEXTJOIN(", ",1,IF(r=C3,ADDRESS(ROW(r),COLUMN(r),4),"")),IF(a="","Not found",a))

Oh, that was a very fast answer. I even forgot that I have three instances of RFI-040 (E8, D11, and C21). Do you have an alternative approach for legacy Excel and how to get the sheet tab in case I have more Excel tabs? Thank you so much.

 

[mb8marmed]

Does this do what you want?

mb8marmed.xlsm
	B	C	D
2	Item	Lookup Value	Cell Ref.
3	1	RFI-0001	Not found
4	2	RFI-024	C5
5	3	RFI-040	E8, D11, C21
6	4	RFI-068	F19
7	5	RFI-xxx	Not found
Sheet2

Cell Formulas
Range	Formula
D3:D7	D3	=LET(r,Sheet1!$B$2:$F$21,a,TEXTJOIN(", ",1,IF(r=C3,ADDRESS(ROW(r),COLUMN(r),4),"")),IF(a="","Not found",a))

Oh, that was a very fast answer. I even forgot that I have three instances of RFI-040 (E8, D11, and C21). Do you have an alternative approach for legacy Excel and how to get the sheet tab in case I have more Excel tabs? Thank you so much.

This one is working on my computer, and this is what I need. However, we are using legacy Excel in our office.

Thank you so much.

 

[Peter_SSs]

Getting the sheet name by formula or vba seem a bit funny since you are already telling Excel to look on a particular sheet. However, see if something like this would suit for all versions.

Function GetAddr(r As Range, s As String) As String
  Dim c As Range
  
  For Each c In r
    If c.Value = s Then GetAddr = GetAddr & ", " & c.Address(0, 0)
  Next c
  If GetAddr = "" Then
    GetAddr = "Not found"
  Else
    GetAddr = Mid(GetAddr, 3)
  End If
End Function

Function GetSheet(r As Range) As String
  GetSheet = r.Parent.Name
End Function

mb8marmed.xlsm
	C		E	F
2	Lookup Value		Cell Ref.	Sheet Tab
3	RFI-0001		Not found	Not found
4	RFI-024		C5	Sheet1
5	RFI-040		E8, D11, C21	Sheet1
6	RFI-068		F19	Sheet1
7	RFI-xxx		Not found	Not found
Sheet2

Cell Formulas
Range	Formula
E3:E7	E3	=GetAddr(Sheet1!B$2:F$21,C3)
F3:F7	F3	=IF(E3="Not found",E3,GetSheet(Sheet1!B$2:F$21))