Location of value in 2d table...

[spacely]

I have a value which occurs in a table. Lots of rows and columns. I just need the row number in which the value occurs. I don't think Match can handle a 2d array.

Dave

 

[Joe4]

Is this what you are looking for?
https://exceljet.net/formula/get-address-of-lookup-result

 

[Peter_SSs]

Does the value only appear once in the table?
Are you looking for something like this?

Excel Workbook
	A	B	C	D	E	F	G	H
3
4		Ken	Peter	Sam
5		Paul	Bill	John		Name of interest	Row	Column
6		Bob	Fred	Jack		Fred	3	2
7		Ben	Tom	Simon
8
Find Row and Column

 

[Marcelo Branco]

See if this example helps

Data in A2:C6
Criteria in E2:E4

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Header1​

[/td][td]

Header2​

[/td][td]

Header3​

[/td][td][/td][td]

Criteria​

[/td][td]

Row​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

10​

[/td][td]

1​

[/td][td]

11​

[/td][td][/td][td]

122​

[/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

9​

[/td][td]

103​

[/td][td]

13​

[/td][td][/td][td]

44​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

44​

[/td][td]

22​

[/td][td]

15​

[/td][td][/td][td]

39​

[/td][td]

Not Found​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

67​

[/td][td]

122​

[/td][td]

17​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

2​

[/td][td]

56​

[/td][td]

8​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in F2 copied down
=IFERROR(SMALL(IF(A$2:C$6=E2,ROW(A$2:C$6)),1),"Not Found")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[spacely]

Thanks all. It's solved. Not sure how to close this thread as solved.....

 

[Joe4]

Thanks all. It's solved. Not sure how to close this thread as solved.....

That is all you have to do, just a reply like that.