Log In
Register
Home
Need both exact and approximate capability with VLOOKUP
DBuck
Seattle, WA
Member
Members
Power Query
Power Query
Forum Posts: 9
Member Since:
June 25, 2016
June 25, 2016
Offline1
June 23, 2018 - 3:56 am
Hello -
Is there a way to return a result for potentially both an exact match or an approximate match using VLOOKUP or Index/match? I can make it work one way or the other, but not both.
As an example, if I enter 259 in the attached sheet (cell I9) I should get 1 as a result, and if I enter 260 I should get 0 as the result. However, entering 260 returns the result from the previous row, 40.
Thanks for any suggestions with this.
Dave
SunnyKow
Puchong, Malaysia
VIP
Members
Trusted Members
Trusted Members
Forum Posts: 1432
Member Since:
June 25, 2016
June 25, 2016
Offline2
June 23, 2018 - 2:28 pm
Hi Dave
See if this is what you are looking for.
=VLOOKUP(I9-1,E9:F30,2,1)
Sunny
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: stuart burge
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
stuart burge
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27294
Member Stats:
Guest Posters: 49
Members: 31919
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —