Can this somehow be done on Lookup or Lookdown?
A number X is in H540 and I want to know if a number > or = exists in the range E541:E1320. If the answer is yes, I'd like to know what row it is in. I'm only interested in the first incidence of the number.
Thanks
=MATCH(TRUE, H541:H1320 >= H540, 0) + ROW(H540)
This is an array formula, which means it MUST be confirmed with Ctrl+Shift+Enter rather than just Enter. You'll know you did it right if curly braces appear around it.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
=MATCH(TRUE,INDEX(H541:H1320>=H540,0),0)+ROW(H540)
normally entered
Last edited by martindwilson; 09-03-2009 at 05:31 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks