cell address of occurence of a vlookup formula
Is there any formula which can give cell address of occurence of a
vlookup formula. Thxs |
cell address of occurence of a vlookup formula
wrote...
Is there any formula which can give cell address of occurence of a vlookup formula. If VLOOKUP(x,Range,n) evaluates to a cell in Range, then CELL("Address",INDEX(Range,MATCH(x,INDEX(Range,0,1 )),n)) gives its address. |
cell address of occurence of a vlookup formula
q 2.00 y 2.00 q 2.00 x 1.00 q 3.00 r 3.00 s 3.00 t 3.00 u 3.00 v 3.00 Range = A1:B10 y 2.00 ok address $B$10 - wrong address with proposed formula Harlan, Thxs a lot for your reply - but I'm getting a incorrect address (should have been B2) Could you pls help Harlan Grove wrote: wrote... Is there any formula which can give cell address of occurence of a vlookup formula. If VLOOKUP(x,Range,n) evaluates to a cell in Range, then CELL("Address",INDEX(Range,MATCH(x,INDEX(Range,0,1 )),n)) gives its address. |
cell address of occurence of a vlookup formula
Thxs a lot Ron!
But can you pls explain to me step by step the reasoning behind the formula as I'm not used to complex formula with index & match. Hope to hear from u again Ron Rosenfeld wrote: On 25 Oct 2005 02:49:29 -0700, wrote: q 2.00 y 2.00 q 2.00 x 1.00 q 3.00 r 3.00 s 3.00 t 3.00 u 3.00 v 3.00 Range = A1:B10 y 2.00 ok address $B$10 - wrong address with proposed formula Harlan, Thxs a lot for your reply - but I'm getting a incorrect address (should have been B2) Could you pls help The MATCH portion of Harlan's formula assumes that Column 1 of Range is sorted in ascending order. Since yours obviously is not sorted that way, his formula should have read: CELL("Address",INDEX(Range,MATCH(x,INDEX(Range,0,1 ),0),n)) or, in your instance: =CELL("Address",INDEX($A$1:$B$10,MATCH("y",INDEX($ A$1:$B$10,0,1),0),2)) --ron |
cell address of occurence of a vlookup formula
|
All times are GMT +1. The time now is 10:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com