Name : Upendra Singh Tip : Left Lookup
One of the Limitation of both the VLOOKUP and HLOOKUP functions is that you can only lookup a value to the right of the key value. For example, in the range shown Below, you can retrieve the value "c" by using VLOOKUP to search for a 3. However, the reverse is not true. It is not possible to use VLOOKUP to search for "c" and return the value 3. This sort of operation is called a left lookup, and is possible only by using the MATCH and OFFSET functions. =OFFSET(G32,MATCH(I32,$G$32:$G$38,0)-1,-1,1,1) The MATCH function tells us where in the list $G$32:$G$38 the value of I32 is, and then the OFFSET function goes to the left ( -1) to retrieve the value. 1 a a <- Lookup Value 2 b 1 <- Actual Answer for applying formula. 3 c =OFFSET(B1,MATCH(D1,$B$1:$B$7,0)-1,-1,1,1) 4 d D1=Lookup Value. 5 e $B$1:SBS7 = Lookup Value to Match in this column. 6 f 7 g Regards, Upendra Singh 9910227325 ---------------------------------------------------------------------------- --------------------------------- There are 10 kinds of people: Those who understand binary and those who don't ----~----~----~----~------~----~------~--~--- --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
