I have a column B with article numbers. I\'d like to match these article numbers
ID: 3564168 • Letter: I
Question
I have a column B with article numbers. I'd like to match these article
numbers with a price in another sheet in the same workbook. My
formula looks like this:
=INDIRECT("Pricelist!E"&(MATCH(B12;Pricelist!A:A;0)))
This formula works perfectly for most of my rows. But where I've
added new rows and copied the formula the cell outputs #N/A as
if the formula didn't find a match.
If I copy the article number in say B12, then go to the Pricelist sheet
and manually search for it CTRL+H the article number is found.
The formula is correct and the matching value is present. Still I get
a #N/A in the cells where this formula is present for all new rows
that I introduced.
What can be wrong???
Explanation / Answer
Actually at the moment I'm not quite sure why it wouldn't work as long as there's a valid entry in column B where you've copied the formula to. But I am wondering if this might not work just as well, if not better for you (assuming formula on row 12)
=VLOOKUP(B12, PriceList!$A:$E, 5, False)
or with your parameter separator being ; vs , then
=VLOOKUP(B12; PriceList!$A:$E; 5; False)
My suspicion about your INDIRECT() formula is that because B12 is not changing, the old matched value is being used and by inserting rows the previously calculated address is no longer valid. An [F9] might fix things, not sure. But in any case, if the VLOOKUP() function works for you, it will be faster and less error prone.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.