From: Fakhrul on 11 Apr 2010 02:17 Thanks! Great Work!!Excellent!!! Vlookup has shortcoming and it would realy overcome that shortcoming. I was looking it several time and did not get it. Then I used VBA and succeed but I wanted functions. Would you please explain TOTAL FUNCTIONS, specially Max(B2:E4=10)? T. Valko wrote: Try this array formula** :A10 = lookup 18-Dec-09 Try this array formula** : A10 = lookup value =INDEX(A2:A4,MAX((B2:E4=A10)*ROW(B2:E4))-MIN(ROW(B2:E4))+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP Previous Posts In This Thread: On Thursday, December 17, 2009 2:20 PM Eric_G wrote: LOOKUP across a whole table and not just first row or column I am attempting to look up a particular value in a table and then identify the number in the first column of that table which corresponds to that row. For example: 111 678 666 475 987 232 565 687 4888 242 4543 123 3334 2727 590 From the above table, be able to enter a value in a formula within a cell which in turn, would return the value in the first column of the row which contains that value. For example, if I enter the value 687 in the cell against which the formula is applied, I wish the formula to provide the value "232" as the response. Similarly, if the value 2727 is entered, the value 4543 should be the value response. Unfortunately, the lookup function does not work against a whole table nor does it provide a value in the same column. Suggestions? Many thanks. On Thursday, December 17, 2009 3:22 PM berniean wrote: You'll need to use Index and Match. You'll need to use Index and Match. There is a good explanation at Contextures: http://www.contextures.com/xlFunctions03.html "Eric_G" wrote: On Thursday, December 17, 2009 3:53 PM T. Valko wrote: Maybe this... Maybe this... A10 = 687 =SUMPRODUCT((B2:E4=A10)*A2:A4) Note this will only work if the table data is unique, In other words, there is only one instance of 687 in the table. -- Biff Microsoft Excel MVP On Friday, December 18, 2009 7:30 AM Eric_G wrote: Thanks for your assistance, but not all the values are, in fact, numeric. Thanks for your assistance, but not all the values are, in fact, numeric. Some are text values so the SUMPRODUCT function does not work. Any other suggestions? "T. Valko" wrote: On Friday, December 18, 2009 11:35 AM T. Valko wrote: Try this array formula** :A10 = lookup Try this array formula** : A10 = lookup value =INDEX(A2:A4,MAX((B2:E4=A10)*ROW(B2:E4))-MIN(ROW(B2:E4))+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP Submitted via EggHeadCafe - Software Developer Portal of Choice Build a Cross-Browser ASP.NET CSS-Only Menu UserControl http://www.eggheadcafe.com/tutorials/aspnet/f32b452c-48ea-4ed8-96ce-d17d1b482676/build-a-crossbrowser-asp.aspx  |  Pages: 1 Prev: Customize accounting formatNext: Unprotect problem