Hello,

I’m trying to get my sales spreadsheet to auto-fill the correct “material cost” when I select a sold item from a dropdown menu. To achieve this I’m using the following formula in the cost cell G4:

**=IFERROR((VLOOKUP(B4,$Sheet2.$A$5:$Sheet2.$E$54,5) C4),0)*

My understanding of what this should achieve:

- “IFERROR(…,0)” returns a numerical

value of 0 instead of N/A - “VLOOKUP(B4,…” identifies B4 as the cell containing a dropdown menu of my products
- “$Sheet2.$A$5:$Sheet2.$E$54” identifies the full dataset of products fleshing out that dropdown menu, i.e. a range of cells (50 rows, 5 columns) on another sheet that lists my products
- “,5)” instructs G4 to identify the corresponding figure in column E of the product dataset
- “*C4)” multiplies that figure by the number in C4 (which is the number of units sold)

So an example of what I want to happen: I choose “Wyvern meadows” from the dropdown menu in B4; VLOOKUP in G4 refers back to sheet 2, finds “Wyvern meadows” in row 8, identifies a corresponding material cost of £0.69237 in column E; that figure is then multiplied by 1, because one unit was sold; and so G4 is automatically filled with £0.69237.

However, that isn’t happening. See my spreadsheet here: REMOVED You can see that the column in question (Sheet 3, column G, highlighted in yellow) returns a chaotic jumble of results. Sometimes it returns the correct figure, but just as often it gets it wrong.

Can anyone please help? I’ve used VLOOKUP in a similar way in columns J & L without problem, so I can’t understand why column G is behaving so differently!

Thanks in advance