我有一個電子表格，可以計算電池的恆定電流需求。我希望能夠從設定的負載持續時間中找到合適的電池，其恆定電流大於或等於我的計算值。
我正在嘗試使用查找表，其中我的“加載持續時間”（黃色）從數組中選擇一列，然後我的查找函數（綠色）找到一個值 >= 我計算的參考值（藍色）。我還想要另一個函數，它可以從與數組中找到的值相同的行中找到模型。
載入持續時間是一個與對應列相符的下拉式選單。我目前有一個選擇數組列的單元格：
=MATCH(C39,'Battery Lookup Table'!B2:O2,0)
我嘗試了多個查找函數來找到最接近的恆定電流值：
=VLOOKUP(C46,'Battery Lookup Table'!B4:O36,MATCH(C39,'Battery Lookup Table'!B2:O2,0),TRUE)
=VLOOKUP(C46,'Battery Lookup Table'!B4:O36,N49)
=(HLOOKUP(C46,'Battery Lookup Table'!S56:AW69,O50,TRUE))
這些查找表中的每一個都會產生相同的值。
我還嘗試將值從小到大排序，因為 VLOOKUP 和 HLOOKUP 很難破解。我也嘗試在一段時間內交換到行，但沒有成功。
型號：=INDEX('Battery Lookup Table'!A4:A36,MATCH(TRUE,'Battery Lookup Table'!M4:M25>=C46,0))
目前，我的查找函數將轉到正確的列，但通常會使用表底部附近的行/值。找到的值都不是最接近的值。
答案1
VLOOKUP匹配一個值第一的範圍的列，並傳遞符合行中範圍的指定列中的值。因此，你的第一個公式
=VLOOKUP(C46,'Battery Lookup Table'!B4:O36,MATCH(C39,'Battery Lookup Table'!B2:O2,0),TRUE)
C46與 range 中的值相符（我認為在您的範例中為 500.8） 'Battery Lookup Table'!B4:B36。這不是您想要匹配的範圍，但它可能解釋了為什麼您得到錯誤的結果。
MATCH事實上，您已經確定了與您的函數相符的正確列。您可以使用該INDEX功能來選擇範圍的一部分。表達方式
INDEX('Battery Lookup Table'!B4:O36,,n)
提供了n所以'Battery Lookup Table'!B4:O36把你的MATCH函數代替n提供可以進行比較的正確值列C46。這樣做會產生表達式：
INDEX('Battery Lookup Table'!B4:O36,,MATCH(C39,'Battery Lookup Table'!B2:O2,0))
為了使事情易於理解，我將這個表達式縮寫為清單。
您現在想做兩件事：
- 找出其中的最小值清單大於或等於
C46 - 找到與該值對應的電池。
這裡的技巧不是使用VLOOKUP()而是它更靈活的“表弟” INDEX(MATCH())。然而，VLOOKUP匹配範圍的第一列上的值並從右側的列中傳遞值，更靈活的版本允許值來自同一列或左側的列。
此外，有 3 種可能的匹配方式MATCH，取決於第三個參數的值是 -1,0 還是 1。Battery Lookup Table'!B2:O2） 。值-1 尋找查找範圍中大於或等於查找值的最小值的位置。這是您的要求，但它要求將查找範圍放置在下降命令。 （值1尋找查找範圍內小於或等於查找值的最大值的位置，並且要求查找範圍按升序排列－這不是你的要求。）
所有值Battery Lookup Table均按升序排列，因此重新組織資料以使行以相反順序顯示將滿足降序條件 - 100G99 將是第一行數據，50G05 將是最後一行。您還需要刪除空白行，因為其中的值將被視為零，因此可能會因違反降序要求而導致問題。
表達方式
MATCH(C46,list,-1))
提供位置清單您正在尋找的值及其實際值（用於顯示在綠色背景單元格中）由以下提供：
=INDEX(list,MATCH(C46,list,-1))
電池型號由提供
=INDEX('Battery Lookup Table'!A4:A46,MATCH(C46,list,-1))
將表達式替換為清單（如上所述）代入這兩個公式會導致表達式變得繁瑣，第一個公式會出現一定程度的重複，並且MATCH(C46,list,-1)兩個公式都會重複計算。這些重複不是好的做法，如果可能的話應該避免。
執行此操作的一種方法是將匹配的列號和行號的值儲存在工作表單元格中。這些分別是：
=MATCH(C39,'Battery Lookup Table'!B2:O2,0)- 表示為n， 和
=MATCH(C46, INDEX('Battery Lookup Table'!B4:B36,,n),-1)- 表示為米
然後查找結果是
INDEX('Battery Lookup Table'!B4:O36,m,n)（在所需負載持續時間內保持恆定額定值）和
INDEX('Battery Lookup Table'!A4:A36,m) （對應電池型號）
在哪裡n和米是包含計算的列號和行號的兩個儲存格參考。