New Posts For Thread: Vlookup help pleasehttps://community.betfair.com/general_betting/go/thread/view/94082/23535494/vlookup-help-pleaseCan any1 help me with the following please:In column B , I have a list of figures which change when my I update other sheets in the workbookIn Column A , the figures never change but the values in column b beside them always do. In cell H4 , I wantAn interesting aside is that if you had 99 in column B and 101 in column B. The closest number to 100 is actually 101 in % terms than what 99 is.So the formula in H2 would give you 101 even though the differencein both cases is only 1.https://community.betfair.com/general_betting/go/thread/view/94082/23535494/vlookup-help-please?post_id=420928402#420928402An interesting aside is that if you had 99 in column B and 101 in column B. The closest number to 100 is actually 101 in % terms than what 99 is.So the formula in H2 would give you 101 even though the differencein both cases is only 1.Sat, 27 Mar 2010 22:04:38 -0500If you are not familiar with excel the name box is the left most box in the formula barif you select Cell H1 you will see in the name box H1In that box type as per previous instructions.When entering an array formula, When you have pasted or typed inhttps://community.betfair.com/general_betting/go/thread/view/94082/23535494/vlookup-help-please?post_id=420928398#420928398If you are not familiar with excel the name box is the left most box in the formula barif you select Cell H1 you will see in the name box H1In that box type as per previous instructions.When entering an array formula, When you have pasted or typed inSat, 27 Mar 2010 20:49:02 -0500IN H4 enter this =MATCH(H2,dat,0)Select all data in column B. In the name box type dat then enterIn cell H1 type 100 In the name box type tar then enterIn cell H2 paste this formula and enter it as an arrayto do this press shift and control and enterhttps://community.betfair.com/general_betting/go/thread/view/94082/23535494/vlookup-help-please?post_id=420928394#420928394IN H4 enter this =MATCH(H2,dat,0)Select all data in column B. In the name box type dat then enterIn cell H1 type 100 In the name box type tar then enterIn cell H2 paste this formula and enter it as an arrayto do this press shift and control and enterSat, 27 Mar 2010 20:23:24 -0500no Jollop that ain't working mate, don't worry I will figure something out. Many thanks to you and everyone for your help!!!!https://community.betfair.com/general_betting/go/thread/view/94082/23535494/vlookup-help-please?post_id=420928386#420928386no Jollop that ain't working mate, don't worry I will figure something out. Many thanks to you and everyone for your help!!!!Sat, 27 Mar 2010 16:24:57 -0500In C1 (this is an array formula so press control&shift&enter not just enter)=INDEX($B1:$B1000,MATCH(MIN(ABS($B1:$B$1000-100)),ABS($B$1:$B$1000-100),0),1)In H8 (or whichever cell you wanted the result in)=OFFSET($A$1:$B$1000,MATCH($C$1,OFFSET($A$1:$B$https://community.betfair.com/general_betting/go/thread/view/94082/23535494/vlookup-help-please?post_id=420928378#420928378In C1 (this is an array formula so press control&shift&enter not just enter)=INDEX($B1:$B1000,MATCH(MIN(ABS($B1:$B$1000-100)),ABS($B$1:$B$1000-100),0),1)In H8 (or whichever cell you wanted the result in)=OFFSET($A$1:$B$1000,MATCH($C$1,OFFSET($A$1:$B$Sat, 27 Mar 2010 15:53:37 -0500it would then be the higher one Jollophttps://community.betfair.com/general_betting/go/thread/view/94082/23535494/vlookup-help-please?post_id=420928362#420928362it would then be the higher one JollopSat, 27 Mar 2010 15:18:11 -0500so you could return two values both as close to 100 ie 90 & 110 ? which one would you want to match with column A?https://community.betfair.com/general_betting/go/thread/view/94082/23535494/vlookup-help-please?post_id=420928354#420928354so you could return two values both as close to 100 ie 90 & 110 ? which one would you want to match with column A?Sat, 27 Mar 2010 15:13:01 -0500no jollop there is no duplicate values and the figures do go up to the 100,000s of thousands and yes though there is figures with two decimal places 2300.44 etchttps://community.betfair.com/general_betting/go/thread/view/94082/23535494/vlookup-help-please?post_id=420928350#420928350no jollop there is no duplicate values and the figures do go up to the 100,000s of thousands and yes though there is figures with two decimal places 2300.44 etcSat, 27 Mar 2010 15:10:34 -0500just out of interest, is it likely there will be two values in colB with the same value and are all values returned in colB less than 100?It could get a bit more complicated if you get 99, 99, 101 etcare the values in colB always whole numbers or arehttps://community.betfair.com/general_betting/go/thread/view/94082/23535494/vlookup-help-please?post_id=420928346#420928346just out of interest, is it likely there will be two values in colB with the same value and are all values returned in colB less than 100?It could get a bit more complicated if you get 99, 99, 101 etcare the values in colB always whole numbers or areSat, 27 Mar 2010 15:04:15 -0500I now get the answer 1 Ian, which is the top cell nowhttps://community.betfair.com/general_betting/go/thread/view/94082/23535494/vlookup-help-please?post_id=420928342#420928342I now get the answer 1 Ian, which is the top cell nowSat, 27 Mar 2010 14:56:38 -0500