EXCEL 資料位置範圍運算

Question

想請問有一欄具波峰形式的數值(非規律0遞增到一個峰值後又非規律遞減到0，
我以IF函數以及MAX函數寫一個尋找某接近值的對應值，
會求出可能在遞增數列裡或是遞增數列裡的值，
所以我想在尋找的範圍值裡就先寫下只搜尋0到遞增數列的峰值這範圍，
所以想請問如核對資料位置範圍作函數取值，
謝謝
如範例圖

Answer 1

@Mini Ma

Please note, currently Q&A forum support language is English, to better help other forum uses who encounter similar issue and read this thread, could you please translate your post in English? Thanks for your understanding.

According to your description, I created a sample like yours.

I used the following array formula =MAX((V1:V21=MAX(V1:V21))*ROW(V1:V21)) to get the row number of max value. The result of my sample is 11.

Then I use the following array formula to get the value that matches the condition and from continuously growing data area. please do not forget to press Ctrl+Shift+Enter.
=MAX(IF(V$1:INDIRECT("V$"&MAX((V1:V21=MAX(V1:V21))*ROW(V1:V21)))<35,V$1:INDIRECT("V$"&MAX((V1:V21=MAX(V1:V21))*ROW(V1:V21)))))

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.