Excel 2007 : Finding and listing valid cells: Randomly fails.

**jcbray** · 04-26-2010, 10:30 PM

Hello forum,
The attachment is a modified version of the doc I am working on, but shows the problem that is cropping up elsewhere.

The problem is the LOOKUP() formula should be looking for the appropriate cell in Roster!L column, and copying the corresponding cell from Roster!B column, it works perfectly for a few, but fails on others, although the only diffrence I can see is the unique reference cell...

Sheet1!A82 should be pulling in "Collins" from Roster!B28 (via Roster!L28) It pulls in all previous names but stumbles on this one for some reason.

In the full version (another version with all 4 days implemented), it also drops names in between others (on other days), so it'll show '0' before continuing on with names. The formula is dragged down, and I can't see a reason why it should stop working *baffled*

Any help on this would be greatly appreciated.

**Huron** · 04-27-2010, 08:09 PM

To use the LOOKUP formula in a single column range, the values must be in ascending order. The values in L22:L33 begin in order but the last few are zeros.

You can use INDEX and MATCH instead eg in A78

=INDEX(Roster!$B$22:$B$33,MATCH(L78,Roster!$L$22:$L$33))

**jcbray** · 04-27-2010, 10:06 PM

Tried your formula, and it works, but only for the first 4 like last time, A82 still pulls up '0'

=INDEX(Roster!$B$22:$B$38,MATCH(L78,Roster!$L$22:$L$33))

And changing $L$33 at the end of that formula to $L$38 causes it to fail as well, which is need for the Thursday column. Although still works changing the earlier B33 to B38.

Thanks for your help mate

**Huron** · 04-28-2010, 03:19 AM

Sorry - the formula should be

=INDEX(Roster!$B$22:$B$33,MATCH(L78,Roster!$L$22:$L$33,0))

The ",0" is needed to find an exact match when the items are not in ascending order.

**jcbray** · 04-28-2010, 09:03 AM

Huron,
Thank you very much for your time, effort and patience. That formula works flawlessly and I'm very appreciative. Apologies for the slow reply, I was eagerly updating my Master book and seeing how pretty and organised it became :P

Mods: You can mark this thread as "Solved"

**Huron** · 04-28-2010, 11:29 AM

I'm just sorry that I didn't give you the right formula the first time.

To mark your thread solved do the following: - Go to the first post - Click edit - Click Advance - Just below the word "Title:" you will see a dropdown with the word No prefix. - Change to Solve - Click Save