ExcelBanter - View Single Post - Cell values not being properly evaluated by VLOOKUP() orSUMPRODUCT()... why?

Martin Brown

On 27/01/2014 01:22, wrote:
Any help would be appreciated. I can't attach the spreadsheet... so please contact me for a copy, if you have time and interest in assisting me. I assure you that I am quite capable of using Excel. I have looked at this for way too long and have no comparable colleagues to turn to...

I have separated the two issues...

VLOOKUP()

I created some Conditional Formatting in the <ILineups</I tab to determine when skater numbers are not on a Home/Away roster. It helps to point out typos.

I have similar conditional formulas in the following columns (C, F, I, L, O). If a value in even rows between 4 and 79 can NOT be found in the database <bHomeNumbers</b, change the font to strikethrough.

In the following columns (Y, AB, AE, AH, AK). If a value in even rows between 4 and 79 can NOT be found in the database <bAwayNumbers</b, change the font to strikethrough.

In both cases, the C (or Y) column behaves differently than the other columns F,I,L,O (or AB,AE,AH,AK). In columns C (or Y), I receive false negatives (strikethrough). Is it a cell format issue? Any ideas?

I suspect one or more of your cases in the list are not in alphabetical
order and/or a fencepost error. Try tacking ",FALSE)" on the end.

SUMPRODUCT()

This might be the same issue. If you go to the LU tab. Some cells (O10 [3], O11 [1], O13 [5], O18 [4], O21 [5]) are returning zero, when they should be returning a positive number [#]. Why aren't the Lineups! values being properly evaluated?

Suspect some of the cells may be formatted as text.

Again, if you have the time and interest, please contact me for a copy of the spreadsheet. With the right eyes, this should not take long at all.

Sean Kelly
Santa Cruz, CA

--
Regards,
Martin Brown