I have a VLookup cell which gives me the wrong value: This is the table:
PID Product Price User User name Deal On Amount After
in 1001 table 1001 1 Milly No 1000
in 1001 table 100 13 Vernetta Yes 900
out 1001 table 50 14 Mireya No 900
out 1001 table 100 15 Rosanne Yes 1000
out 1001 table 101 16 Belinda No 1000
in 1001 table 200 1 Milly Yes 800
in 1234 chair 300 2 Charlena Yes 500
in 1234 chair 100 3 Mina Yes 400
in 1234 chair 50 4 Sabina Yes 350
in 8231 couch 20 5 Joni Yes 330
in 1001 table 150 6 Armando Yes 180
in 1001 table 100 7 Noemi Yes 80
in 8231 couch 40 8 Ashlie Yes 40
in 8231 couch 30 9 Ann Yes 10
out 1001 table 201 10 Angelina Yes 211
out 1234 chair 300 11 Melvina Yes 511
out 8231 couch 21 12 Mattie Yes 532
The product column is a VLOOKUP with the following formula
VLOOKUP(B6,$L$2:$M$10, 2)
B is the PID column. The table in L2:M10 is the following:
PID Prodcut
1001 table
1234 chair
8231 desk
2311 closet
9182 book_shelf
1822 bed
1938 coffee_book_table
2229 couch
Now as you can see. PID 8231 is a desk, but it appears as a couch. Can you see what the problem is?
Answer
The crux of your problem is the way you've written the formula, you forgot the last parameter, "FALSE" or 0 which means you want an EXACT MATCH. So your formula should look like this:
VLOOKUP(B6, $L$2:$M$10, 2, FALSE)
OR
VLOOKUP(B6, $L$2:$M$10, 2, 0)
Both do the same thing.
The default setting for it is TRUE, which looks for the closest match, which you only want if the index you're looking up off of is sorted.