mercredi 6 avril 2016

excel, IF vlookup is true then

I have a spread sheet with 3 different sheets, Data, Prices, and Adjust.

The Data tab looks like this:
enter image description here

The values that are there are brought in from the prices sheet (which is generated by a third party source) by using the following formula

=IFERROR(VLOOKUP($A30,OFFSET(prices!$A:$GC,0,MATCH(K$5,prices!$1:$1,0)-1),3,FALSE),"")

This formula works fine however some of the values generated by the third party source are wrong, so I made the third sheet 'adjust' to account for this (this tab I can format in whichever way makes solving this problem easier), the adjust sheet looks like this:enter image description here

This shows the heading that contains some faulty data and the dates which are faulty, the factor is what the third party data is off by.(this remains consistent across the dates but different for each heading) Notice this only has two of the headings included ENI-MIL and G-MIL, and different dates and factors for each.

What I want to do is add to the formula in the Data sheet so it checks if the heading is in the adjust sheet, then if it is multiply the value it is getting from the prices sheet by the factor under the heading in the adjust sheet on all the dates that fall below it.

Ive tried this a few different ways I keep getting stuck and I cant think of a way to achieve it with a formula in a single cell. I think it should be an IF with a vlookup that changes the value if the vlookup value is true, but I am not sure how to do this.

Aucun commentaire:

Enregistrer un commentaire