I’ve got the following table:
And am looking to use the item weight in column L, find the corresponding value in column O and extract the price in column P to add to the item row in column J.
I’ve written the following formula but the quick fill and fill options aren’t carrying the formula down correctly and are moving all the row numbers down, creating an error.
=IF(MATCH(L2,O2:O17,0),INDEX(P2:P17,MATCH(L2,O2:O17,0)))
Is there a way to avoid this or a better formula that would avoid this?
>Solution :
You can use the ‘locked reference’ function in Excel by adding ‘$’:
=IF(MATCH(L2,$O$2:$O$17,0),INDEX($P$2:$P$17,MATCH(L2,$O$2:$O$17,0)))