Wow, thanks for your hard work. It looks so complicated. To be honest, I
dont feel comfortable with the formula that you provided
(=INDEX(B$1:B$12,MATCH(1,(A$1:A$12=D1)*(B$1:B$12< ""),0))
Just wondering if it's possible to modify the formula I had previously.

=LOOKUP(2,1/(A1:A20=D1),B1:B20)

Thanks,
Aline
--




"Max" wrote:

"Aline" wrote:
Thanks Max,
It works.

Swell. Do take a moment to press the "Yes" button in that response (like the
ones below).

One more question, how can I make it work if on B column is not number
(8,9...) but such as Yes (or No) instead.

Paste into E1's formula bar, then array-enter** the formula below, ie press
CTRL+SHIFT+ENTER to confirm the formula, instead of just pressing ENTER:
=INDEX(B$1:B$12,MATCH(1,(A$1:A$12=D1)*(B$1:B$12<" "),0))
Copy E1 down. Adapt the ranges to suit.

**If you did the CTRL+SHIFT+ENTER confirmation (the "array-enter") properly,
you should see Excel wrap curly braces around the formula in the formula bar,
viz. it'll look like this:
{=INDEX(B$1:B$12,MATCH(1,(A$1:A$12=D1)*(B$1:B$12< ""),0))}

If you don't see the curlies, just re-click inside the formula bar,
re-do the CTRL+SHIFT+ENTER confirmation
Then look again that the curlies are there

If the formula is not array-entered, it will not return the correct result.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---