Nov 04 2022 06:12 AM
Hi there,
I really hope someone can help me out!
The formula i have plugged in is:
= IFERROR(VLOOKUP(D4,Rates!$K$4:$L$725,4,FALSE),"0")*F4
There are two sheets: sheet1 and Rates.
|
| D | E | F | G |
3 |
| Code | Day Rate | Qty | Total |
4 |
| N300 | (ignore this please) | 2 | 0 |
On sheet 1 ^^, I am trying to calculate a total price by multiplying a code (e.g. N300) by a Qty (e.g. 2).
On sheet 2 v v (rates), I have a long list of codes next to rates, that I want to automatically transfer onto sheet 1, that can be multiplied by a quantity.
rates | K | L |
3 | Contract 1 | Rate |
4 | DR1 | £X |
5 | N300 | £Y |
6 | TRR001 | £Z |
... | ... | ... |
725 | DAY003 | £U |
I have already set up data validation to allow these codes to be in a drop down on sheet 1.
Essentially, what is wrong with my formula? The total just comes out at 0, suggesting the formula is correct, but its not doing what I want.
Please help me if you can!
Thanks.
Nov 04 2022 06:26 AM
VLOOKUP's nature is to look left, return right unless you nest a function like CHOOSE to pick the column order.
Try this:
= IFERROR(VLOOKUP(D4,CHOOSE({1,2},Rates!$L$4:$L$8,Rates!$K$4:$K$8),2,0),0)*F4