Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset 2nd ref.
A B
ONE 1 TWO 2 THREE 3 ONE 4 Is there a way I can use the offset to look up a duplicate reference? To where I ref ONE and come up with 4. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset 2nd ref.
Try the below array formula which will lookup and return the 2nd matching value
=INDEX(B1:B10,SMALL(IF(A1:A10="One",ROW(A1:A10)),2 )) With text 'One" in cell C1 and the instance number in cell D1 (array entered using Ctrl+Shift+Enter instead of Enter) =INDEX(B1:B10,SMALL(IF(A1:A10=C1,ROW(A1:A10)),D1)) will return the last matching value in B if data is continuous (just works for your sample). =LOOKUP("one",A:A,B:B) =VLOOKUP("one",A:B,2,1) -- Jacob "houAstros1989" wrote: A B ONE 1 TWO 2 THREE 3 ONE 4 Is there a way I can use the offset to look up a duplicate reference? To where I ref ONE and come up with 4. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset 2nd ref.
Hi,
This array formula (Ctrl+Shift+Enter) will return the value against the last occurrence of One. B10 contains One. I have assumed that the data is in range B4:C7 =INDEX($B$4:$C$7,1*MAX(($B$4:$B$7=B10)*(ROW($B$4:$ B$7)-ROW($B$3))),2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "houAstros1989" wrote in message ... A B ONE 1 TWO 2 THREE 3 ONE 4 Is there a way I can use the offset to look up a duplicate reference? To where I ref ONE and come up with 4. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset 2nd ref.
Hi,
You may also try this non array formula =LOOKUP(2,1/($B$4:$B$7=B10),C4:C7) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "houAstros1989" wrote in message ... A B ONE 1 TWO 2 THREE 3 ONE 4 Is there a way I can use the offset to look up a duplicate reference? To where I ref ONE and come up with 4. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset Q | Excel Worksheet Functions | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Offset | Excel Worksheet Functions | |||
OFFSET HELP | Excel Worksheet Functions | |||
Offset | Excel Worksheet Functions |