Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-13-2012, 01:59 AM
hanvyj hanvyj is offline Windows XP Office 2003
Advanced Beginner
 
Join Date: Feb 2012
Posts: 33
hanvyj is on a distinguished road
Default something like VLOOKUP

I'm checking a value against a table, I need behavior like a VLOOKUP table except I wan't to take the value less than the table value.

For example, I have a value, say 230. I want to look up which of these its closest to, but less than:

1 110
2 220
3 340
4 460
5 503
6 620
7 700
8 890
9 910


10 1120

So I wan't to return "3", as its less than 340. I can't use VBA. Does anyone know how I can do this?

Cheers
Reply With Quote
  #2  
Old 03-13-2012, 02:23 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 381
Colin Legg will become famous soon enough
Default

Hi,

=INDEX(A1:A10,MATCH(D1,B1:B10)+(LOOKUP(D1,B1:B10)< D1))

where A1:B10 contains the table you posted and D1 contains the value to look up (eg 340).
Reply With Quote
  #3  
Old 03-13-2012, 02:38 AM
hanvyj hanvyj is offline Windows XP Office 2003
Advanced Beginner
 
Join Date: Feb 2012
Posts: 33
hanvyj is on a distinguished road
Default

Quote:
Originally Posted by Colin Legg View Post
Hi,

=INDEX(A1:A10,MATCH(D1,B1:B10)+(LOOKUP(D1,B1:B10)< D1))

where A1:B10 contains the table you posted and D1 contains the value to look up (eg 340).
Thank you, that works really well!
Reply With Quote
  #4  
Old 03-13-2012, 08:47 AM
Catalin.B Catalin.B is offline Windows Vista Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Rom‚nia
Posts: 386
Catalin.B is on a distinguished road
Default

Off topic: Congratulations Colin, just noticed you are a Super Moderator now... I was wondering how is Paul dealing alone with all the work
Reply With Quote
  #5  
Old 03-13-2012, 09:03 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 381
Colin Legg will become famous soon enough
Default

Thanks Catalin!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Vlookup & IF together thelauncher Excel 5 08-25-2013 11:32 PM
Vlookup ibrahimaa Excel 5 03-04-2012 11:24 AM
Vlookup ibrahimaa Excel 8 01-03-2012 09:32 PM
Vlookup Karen615 Excel 4 09-12-2011 02:30 PM
Help with VLOOKUP sakhtar Excel 2 07-24-2010 07:39 PM


All times are GMT -7. The time now is 01:00 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft