Search for a value and return the row and column

[levy77]

Is there a way to search for an exact match within an array of data and return the row and/or column of the match? The formula is to go in cells B2 and B3 in the first table.

Search term:	45
Result (column):	4
Result (row):	3

<tbody>
</tbody>

23	43	37	23	67
67	88	87	24	54
74	24	56	45	88
76	24	56	98	65
21	34	79	76	43

<tbody>
</tbody>

 

[kweaver]

What do you expect to happen if the value is not unique?

 

[dazkangel]

Try:
Column=AGGREGATE(16,6,COLUMN(A:E)/(A5:E9=E1),1)
Row=AGGREGATE(16,6,ROW(5:9)/(A5:E9=E1)-4,1)

 

[levy77]

All values will be unique, if I have accidentally put one in which is the same I hope an error appears.

Thanks daz, I ended up making a formula which is =SUMPRODUCT((C1=Credit!$A$2:$Z$100)*COLUMN(Credit!$A$2:$Z$100)). Ill try yours out as well and see how it goes.

cheers

 

[dazkangel]

You're welcome!