Selected Answer
Hi Willie
I think the problem arises from two things:
1) Your column B (team name) is sorted alphabetically but the abbreviations in column A are not for the teams starting L- sort them alphabetically A-Z elsewhere and the sequence will show:
L VEG
LA C
LA R
2) You're using VLOOKUP on that column and (in default mode) that returms an approximate match (which is often right, especially if the search column is sorted alphabetically A-Z).
To see that, look at red cell AB1, whose formula is equivalent to yours elsewhere but just looking up "L VEG" directly in column 1 and returning the result from the same column:
=VLOOKUP("L VEG",A6:A37,1)
It returns KC (not L VEG) since VLOOKUP gets to "LA C" and thinks it's passed anything like L VEG (which is alphabetically before it) so gives the approximate (but wrong) match of KC.
That's fixed in green cell AC1 whose formula is identical but includes the missing, optional third argument:
=VLOOKUP("L VEG",A6:A37,1,FALSE)
where the "range lookup" argument of FALSE means it returns an exact match or an error if not found. Here it correctly shows L VEG
Look further down to green cell AF32. Its formula was
=IF(AE32="","",IF(AE32="H",VLOOKUP(AD32,$A$6:$H$37,5),VLOOKUP(AD32,$A$6:$H$37,7)))
which returned a 0 as you said. Now it's:
=IF(AE32="","",IF(AE32="H",VLOOKUP(AD32,$A$6:$H$37,5,FALSE),VLOOKUP(AD32,$A$6:$H$37,7,FALSE)))
and appears blank.
Hope this fixes things or at least points you in the right direction. In Excel 2016, sadly you can't write a formula using XLOOKUP which is slightly better.
