Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup nth entry
Any formula or UDF to use VLOOKUP so as to return the nth (for example 6th
entry) feedback when there are more than one matching entries in the lookup array? -- Best Regards, Faraz |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup nth entry
Hi Faraz
Try the below array formula Lookup value in C1 Lookup array A1:B1000 The last entry denotes the nth entry (in this example it is 2) =INDEX($B1:$B1000,SMALL(IF($A$1:$A$1000<$C1,"",($ A$1:$A$1000=$C1)*ROW($A$1:$A$1000)),2)) If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: Any formula or UDF to use VLOOKUP so as to return the nth (for example 6th entry) feedback when there are more than one matching entries in the lookup array? -- Best Regards, Faraz |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup nth entry
On Wed, 26 Aug 2009 21:26:05 -0700, Faraz A. Qureshi
wrote: Any formula or UDF to use VLOOKUP so as to return the nth (for example 6th entry) feedback when there are more than one matching entries in the lookup array? Try the following formula =INDEX(B1:B50,SMALL(IF(A1:A50=C1,ROW(A1:A50)),C2)) Note: This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. A1:A50 is the leftmost column of your table array. B1:B50 is where you results are C1 is where your lookup value is C2 is where your n, e.g. 6, is Change these parameters to adapt to your worksheet. Hope this helps / Lars-Åke. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Last column entry lookup. | Excel Worksheet Functions | |||
HELP SVP- EIYHER OR ENTRY OR LOOKUP | Excel Worksheet Functions | |||
Lookup last entry in column | Excel Discussion (Misc queries) | |||
lookup misses the first entry in table | Excel Worksheet Functions | |||
Double entry lookup | Excel Worksheet Functions |