Forum Discussion
VLookup returning incorrect results
I can't understand why this small section of a spreadsheet returns the wrong values. Tried Lookup and Vlookup and get the same result.
=LOOKUP(A23+0.01,'FA Devices'!$B$8:$B$710,'FA Devices'!$C$8:$C$710)
20 Unit 53 Clipso
21 Unit 52 Mr Simms
22 Unit 54 Dental Surgery
23 Unit 54 Dental Surgery
24 Unit 54 Dental Surgery
25 Unit 37 Bakers & Bari
This is the FA Devices Sheet
123 21.0 Unit 52 Mr Simms
124 21.02 UNIT 52 FIRE
125 21.02 UNIT 52 FAULT
126 22.0 Unit 54 Dental Surgery
127 24.02 UNIT 54 FIRE
128 23.0 Unit 51 Waffle Wands
129 23.02 UNIT 51 FIRE
130 23.02 UNIT 51 FAULT
132 24.0 Unit 50 Elements by Nature
133 24.02 UNIT 50 FIRE
139 25.0 Unit 37 Bakers & Bari
1 Reply
The problem is that the list is not in an increasing order so at line 127 you have 24.02 and then numbers <= to that until you reach line 139. I wasn't able to recreate your problem in my 365 account/version as all 3 options: Lookup, VLookup, and XLookup worked 'correctly':
but basically each of the answers you listed were the first case that the value was <= the target value and the next line had a value > the target. In some ways you are lucky it was even that 'clean' because the search paradigm could also find 'local solutions' in the middle of the list. If you use XLOOKUP you have options and it will more accurately/reliably find what you want (the option I show in column N). But assuming you do NOT have 365 or access to these new functions then I suggest you make it find the EXACT match (i.e. do NOT add 0.01). I'm not sure why you are adding 0.01 but if it is due to some roundoff/decimal places you might need to ROUND( 'FA Devices'!$B$8:$B$710,2)
