Try to make it fail
Give the formula a made-up code, knowing what should come out, and find out what it does when it can't find what it's looking for.
Free course. Your Maestro, an AI, .
Checking rows tells you whether the ones you looked at are right. It doesn't tell you what the formula does in a case you didn't happen to see. For that, the second step is to give it a hard case on purpose, knowing ahead of time what should come out.
Step 2: try to make it fail
If you give it a code that isn't in Precios, the formula should tell you it didn't find it. In an empty cell, enter the same formula with a made-up code, in quotes:
=VLOOKUP("P-999",Precios!$A$2:$C$9,3)=BUSCARV("P-999",Precios!$A$2:$C$9,3)If your Excel separates the parts with semicolons, change each comma to ;.
Before you press Enter, decide what should come out. That's the important part. A test only works if you know the answer before you run it.
It should give you #N/A, which is how Excel says "not found." It gives you 35, the price of the horchata, the last code on the list.
If you hadn't decided beforehand what you expected, 35 would have looked like any other price. That's why you test with a value whose answer you already know.
Why it gives 35
VLOOKUP has a fourth, optional part. If you leave it out and it doesn't find the code, it settles for the closest one that comes before it on the list. Since P-999 comes after every code, it settles for the last one. It doesn't warn you: it gives you a price that looks normal. It's an error of the second kind.
To make it require the exact code, add FALSE at the end. Separately, if you use A:C instead of $A$2:$C$9, it also finds products you add later:
=VLOOKUP(A2,Precios!A:C,3,FALSE)=BUSCARV(A2,Precios!A:C,3,FALSO)If your Excel separates the parts with semicolons, change each comma to ;.
XLOOKUP (BUSCARX in Spanish), the function Excel released later for the same job, looks for the exact value unless you tell it otherwise.
But the lesson isn't VLOOKUP. With any formula, ask yourself what it should give you for a value that isn't there. A code that doesn't exist, an empty cell, a date outside the range, a zero. If you know what should come out, test it and compare.
Try it
- In an empty cell in your file, type the AI's formula with
"P-999". Before you press Enter, write down what you expect. - Compare what came out with what you expected.
- Now try the corrected formula, with FALSE, and the same made-up code. What comes out?
- Think of another formula you use at work. What value would you give it to try to make it fail, and what should come out?
A good answer says what you expected, what came out, and why they didn't match with the first formula. For item 4, name the case and its expected answer.
Tell your Maestro which case you'd test in your formula.