Forum Discussion

Copper Contributor

Jun 02, 2022

Solved

#REF! error in INDEX/MATCH formula

Dear all, Can someone help me with my formula? =INDEX('[COSTED_BOM_RFQ_REDUCED__FULL_SMART_LCU_20220523 (1).xlsx]Sheet0'!K11:K93;MATCH(C8;'[COSTED_BOM_RFQ_REDUCED__FULL_SMART_LCU_20220523 (1).xl...

excel

Formulas and Functions

Martin_Weiss
Jun 02, 2022
Hi Caro_Bloggs

the first part of your INDEX formula refers to just one column (K11:K93), but with the very last argument you refer to colum 11.
So if you want to return the value of column 11, the INDEX range should at least cover K11:U93

Martin_Weiss

Bronze Contributor

Jun 02, 2022

Hi Caro_Bloggs

the first part of your INDEX formula refers to just one column (K11:K93), but with the very last argument you refer to colum 11.

So if you want to return the value of column 11, the INDEX range should at least cover K11:U93

Caro_Bloggs

Copper Contributor

Jun 02, 2022

Great thanks this is really helping. I changed my formula to:
=INDEX('[COSTED_BOM_RFQ_REDUCED__FULL_SMART_LCU_20220523 (1).xlsx]Sheet0'!A11:X93;MATCH(C8;'[COSTED_BOM_RFQ_REDUCED__FULL_SMART_LCU_20220523 (1).xlsx]Sheet0'!B11:B93;0);11)

and got now the right expected answer, thank you!
I wish you a nice day!
Caro