Get row and column numbers for given searched value in Table or Range

[monzer_yazigi]

I have a large random data, given a search value as input, I need to:
1. search for its' next greater value within the random data
2. return the row and column of the next greater value

The first part is solved by the formula:

LARGE(A1:H1000,COUNTIF(A1:H1000,">=" & K1))​

where:

- A1:H1000 is the range of random data​

- K1: Search value​

The problem is with the second question. Not able to figure it!!!

I know I can use VBA but I am trying to avoid that.

Any advise?

Thanks

 

[Saba Sabaratnam]

Once you work our your max value, you can use this formula in C1



=ADDRESS(SUMPRODUCT(--((A5:F7=B1))*ROW(A5:A7)),SUMPRODUCT(--((A5:F7=B1))*COLUMN(A5:F5)))

Kind regards

Saba

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can values occur more than once?

 

[monzer_yazigi]

Once you work our your max value, you can use this formula in C1



=ADDRESS(SUMPRODUCT(--((A5:F7=B1))*ROW(A5:A7)),SUMPRODUCT(--((A5:F7=B1))*COLUMN(A5:F5)))



View attachment 43101

Kind regards

Saba

Thank you. It worked perfectly. It fail only if there are repeated values as questioned by Fluff.
My data does not have repeated values anyway.

 

[monzer_yazigi]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can values occur more than once?

Thanks for the note. Done.
No it does not repeat. The offered solution by Saba worked correctly. However, it fails when repeated values are there.
For me, since my data does not have repeated values, the solution worked well.

 

[Fluff]

Glad it works & thanks for updating your account.