Forum Discussion

Brass Contributor

Aug 15, 2021

Solved

Populating cell with value in immediate criteria match

Dear all, I have attached a spreadsheet containing raw data set for a fuel control spreadsheet. We have 4 types of vehicles: - Trucks, we dispense the exact amount of fuel according to the dest...

Copy of raw data.xlsx617 KB

excel

Formulas and Functions

Riny_van_Eekelen
Aug 15, 2021
vitoaiaco That formula breaks because you are trying to look up the 8th column in a range that contains only 5 columns (E through I).

vitoaiaco

Brass Contributor

Aug 15, 2021

I have tried the following formula in column M, but it doesn't seem to work:

=IF(OR(Q5="EARTHMOVING",Q5="BUSES"),(VLOOKUP(E5,E6:I$9999,8,FALSE)),L5-K5)

Riny_van_Eekelen

Platinum Contributor

Aug 15, 2021

vitoaiaco That formula breaks because you are trying to look up the 8th column in a range that contains only 5 columns (E through I).

vitoaiaco
Brass Contributor
Aug 15, 2021
Thank you for the reply. I have expanded my lookup range from E:L. It appeared to popular the cell correctly, but if I change the volume value it doesn't automatically change the value in column M. Would you have any idea why this happens?
- Riny_van_Eekelen
  Platinum Contributor
  Aug 15, 2021
  vitoaiaco Sorry, don't follow. Cell M9 will return 119.00 with the adjusted formula. This is the number found in L19 for vehivle number "LD 02". Correct? What volume (e.g. in which column/cell) doesn't change the value in M?
  - vitoaiaco
    Brass Contributor
    Aug 15, 2021
    Riny_van_Eekelen Yes, that's correct... As it stands, Cell M9 will CORRECTLY return 119.00 with the adjusted formula. Let's say you change the value in L19 to 200 instead of 119. The value in M9 will still be 119.00 UNLESS I manually refresh the formula in M9. Is there a way to automatically refresh the formula to populate according to the latest entry?
    
    Many Thanks
    Vito