Hi,
I am using a XLOOKUP that works perfectly (top line below), yet modifying it in another cell gives an N/A error. The only difference is the first one is looking up the value in I15 and the second is looking up the value in L15. I have triple-checked that the value in L15 is the same as in the range being searched.
Why does it work in one instance but not the other??
=XLOOKUP(ProdSel&$D17,'Prod Price List'!$A$3:$A$57&'Prod Price List'!$B$3:$B$57,XLOOKUP(ListSel&'Bulk Prods GP Calculator'!$I$15,'Prod Price List'!$D$1:$R$1&'Prod Price List'!$D$2:$R$2,'Prod Price List'!$D$3:$R$57))
=XLOOKUP(ProdSel&$D17,'Prod Price List'!$A$3:$A$57&'Prod Price List'!$B$3:$B$57,XLOOKUP(ListSel&'Bulk Prods GP Calculator'!$L$15,'Prod Price List'!$D$1:$R$1&'Prod Price List'!$D$2:$R$2,'Prod Price List'!$D$3:$R$57))
Thanks in advance..
Hi Alec,
Please supply your workbook, hard to debug this without your data.
Phil
Hi Phil,
I have found the problem.. The first horizontal search range (D1:R1) is three groups of merged cells. Once I un-merged them and copied the data across each group, the formulae work fine. It is still odd as to why the first one worked, but at least I have the answer (Merging is evil)
cheers
Yes, merged cells are evil!
