Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Formula result showing 0 but for only one entry

0

Hello all,

Hopefully someone can solve this delimma for me. I built a file (Excel 2016, attached) to track how well I do with my weekly football picks. Everything works as expected except for one entry. It happens only with "L VEG" entry (all 18 weeks) in the "OPP" (opponent) column. When "L VEG" is entered in the "OPP" column the "W" (win) column to the right shows "0". It doesn't matter which row "L VEG" is entered in. The formula in the "W" works as expected for any other team entry in all rows. I have checked the formulas in columns E, F, G, & H and they are all the same. I just can't figure this out.

Why is this happening with "L VEG" only? Any help will be appreciated.

Answer
Discuss

Answers

0
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.

Discuss

Discussion

Glad that seems to have worked- thanks for selecting my Answer, Willie.
John_Ru (rep: 6297) Sep 11, '23 at 8:24 am
Hi John,

I was hoping you would come to my rescue.
I had suspected it had something to do with the VLookUp formula but I just wasn't seeing it. I've added that third arguement (False) and presto - all is as expected.
Also, thank you for your detailed and concise explanation.

Cheers   :-)
WillieD24 (rep: 567) Sep 11, '23 at 8:29 am
You're welcome Willie, any time. I had something similar happen to me a few years back and I was just baffled!
John_Ru (rep: 6297) Sep 11, '23 at 8:33 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login