Excel中基于行列输入值在指定区域定位单元格的问题

Got it, let's break down why your formula isn't working and get it sorted out—your goal is totally doable, we just need to adjust how we handle the text-based ranges and prefixed input values.

First, Let's Diagnose the Issues #

Your original formula =INDEX(C4:J11,MATCH(B15,A4:B11,1),MATCH(B14,C3:J3,1)) fails for two key reasons:

• Input values have text prefixes: BA=0.31 and GF=32 can't directly match the numeric ranges in your table (like 0.3-0.34 or 30-60).
• Column ranges are in descending order: Your column headers (>90, 61-90, 30-60) are sorted from highest to lowest, but the 1 in MATCH assumes ascending order for approximate matches.

Step-by-Step Fix #

Let's build the corrected formula piece by piece:

1. Extract Numeric Values from Inputs

First, we need to strip out the BA= and GF= prefixes to get pure numbers:

• For the row input (B15: e.g., BA=0.31): --SUBSTITUTE(B15,"BA=","") converts the text to a numeric value (0.31).
• For the column input (B14: e.g., GF=32): --SUBSTITUTE(B14,"GF=","") converts to 32.

2. Match Rows (BA Ranges)

Your row ranges are stored as text (e.g., 0.3-0.34). We'll extract the minimum value of each range to use for approximate matching (since your rows are sorted ascending, MATCH with 1 works here):

• To get the min value from a range like 0.3-0.34: --LEFT(A4,FIND("-",A4)-1)
• The row MATCH becomes: MATCH(--SUBSTITUTE(B15,"BA=",""),--LEFT(A4:A11,FIND("-",A4:A11)-1),1)

3. Match Columns (GF Ranges)

Your columns are sorted descending, so we need to use -1 for approximate matching. We also need to handle the >90 range by assigning it a large numeric value (like 1000) so values over 90 match it:

• The column MATCH becomes: MATCH(--SUBSTITUTE(B14,"GF=",""),IF(LEFT(C3:J3,1)=">",1000,--LEFT(C3:J3,FIND("-",C3:J3)-1)),-1)

Final Corrected Formula #

Put it all together in INDEX:

=INDEX(C4:J11,
 MATCH(--SUBSTITUTE(B15,"BA=",""),--LEFT(A4:A11,FIND("-",A4:A11)-1),1),
 MATCH(--SUBSTITUTE(B14,"GF=",""),IF(LEFT(C3:J3,1)=">",1000,--LEFT(C3:J3,FIND("-",C3:J3)-1)),-1)
)

• Note for older Excel versions: If you're not using Excel 365/2021, enter this formula and press Ctrl+Shift+Enter to confirm it as an array formula (it will wrap in {} automatically).

Quick Test Example #

• Input BA=0.31 → extracts 0.31, matches the 0.3-0.34 row (second row in your data range)
• Input GF=32 → extracts 32, matches the 30-60 column (third column in your header range)
• The formula returns 10 g, which matches your table's corresponding cell—perfect!

Bonus: Handle Input Spacing #

If your inputs might have extra spaces (e.g., BA= 0.31), add TRIM to clean it up:

=INDEX(C4:J11,
 MATCH(--TRIM(SUBSTITUTE(B15,"BA=","")),--LEFT(A4:A11,FIND("-",A4:A11)-1),1),
 MATCH(--TRIM(SUBSTITUTE(B14,"GF=","")),IF(LEFT(C3:J3,1)=">",1000,--LEFT(C3:J3,FIND("-",C3:J3)-1)),-1)
)

内容的提问来源于stack exchange，提问作者Brian Le