#1
|
|||
|
|||
Vlookup formula no longer producing proper result
See Attachment
In the Q1 table, I have three fields related to the Glucose reading. (D) Libre reading (continuous monitoring sensor) (E) Blood reading (F) Calibrated Glucose value I divide the blood reading by the Libre reading to produce a calibration factor. This factor is stored in the "Glucose Factor" table by start date. This calibration factor varies from one sensor to the next. Each sensor lasts 14 days. I need to re-calibrate each new sensor after it is installed. The factor usually varies +- around 1.0. The factor for several of the previous sensors was .9x. The factor for the current reading is 1.03. The Glucose Column contains a formula which looks up the factor in the "Glucose Factor" table and multiplies the Libre reading by that factor for the current date to produce the calibrated Glucose result. As you can see in row 3, the formula is not producing the proper result. The calibrated Glucose reading should be near 220 (219 in this case). However the formula is producing a result for a factor of .95. This is the same factor for row 29 in the Glucose Factor table beginning on 21 Jul 2023. For some reason, the VLookup function is no longer returning the correct value. It has been working for over a year. Is there a limit to the number of rows that can be in the Glucose Factor table? What has happened between Jul and Sep? FYI: Row 2 is hidden to preserve formatting when I insert new rows at the top. |
#2
|
||||
|
||||
The formula in column F refers to a named range GlucoseFactor. There are in fact 2 such named ranges, one Workbook-scoped and one Sheet-scoped. One refers to:
='Glucose Factor'!$A$2:$B$29 the other to: ='Glucose Factor'!$A$2:$B$32 I suggest you change the formula to: =IF([@Libre]=0,[@Blood], IF([@Libre]>0,[@Libre]*VLOOKUP([@Date],Table4,2),0)) |
#3
|
|||
|
|||
On sheet Glucose Factor you have a Defined Table Table4, which expands automatically whenever you add new rows to this table. You also have defined a Named Range ClucoseFactor='Glucose Factor'!$A$2:$B$29, which defines a range of table on sheet Glucose factor from rows 2:29. This named range is fixed one - remains same when you add new glucose factor values. In your formula, you refer to this fixed range! Replace it with reference to Defined Table instead (like p45cal adviced)!
Code:
=IF([@Libre]=0,[@Blood], IF([@Libre]>0;[@Libre]*VLOOKUP([@Date],Table4,2),0)) |
Tags |
vlookup |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Vlookup - if the result is blank then enter a formula | srsev6 | Excel | 5 | 03-01-2019 07:48 AM |
Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula | innkeeper9 | Excel | 2 | 09-13-2016 08:59 PM |
How To Move Focus From VLOOKUP Result to Report Sheet | swindon.expat | Excel | 2 | 04-10-2016 12:15 AM |
formula, 2 criteria, one result | cjdstephenson | Excel | 4 | 06-02-2015 09:17 PM |
Fetching more than one result with VLookup on multiple sheets | lilvillaf | Excel | 2 | 06-12-2014 05:39 PM |