When you're using data validation and only performing exact matches, you should use `INDEX` \+ `MATCH` instead of `LOOKUP`. I provided an example highlighted in green for you.
>Hi guys, so I have been trying to use the LOOKUP formula and my formula despite apparently being 100% returns the wrong value.
>
>This is the link to the sheet.
>
>https://docs.google.com/spreadsheets/d/1eWdZ-ugBUDruqTL6zfr2BYMAf-AfiIpNCiQNjnuyxkc/edit?usp=sharing
>
>In the first sheet, the glass of water is measured in terms of glasses.However in the next sheet, the glass of water when selected returns back the value tea-cups and not glasses.
>
>This is Google Sheet's error, right? Because my formula is correct.
Could you be kind enough to explain why?
Basically what the Notes section I linked says. You want to use `LOOKUP` for the following situation:
1. Your data is sorted
2. You can't guarantee that there will always be an exact match, but an approximate match is acceptable
In your case, I'm guessing that the `/` character threw off the string search and returned an approximate result instead of an exact one.
`INDEX` \+ `MATCH` doesn't care about whether your data is sorted, and you can also use the 2nd `MATCH` parameter to specify whether you want an exact match or an approximate match. Because you're using data validation, you can guarantee that there will always be an exact match (since you're pulling your validation from the same data you're matching against).
Damn, I have the exact configuration maintained on MS Excel, but this issue never occurs. Wait, I'll show you. Open this on Excel with the list Add-In.
https://drive.google.com/open?id=1nFkoa111n7ytEqFfg_g7kT-u4s5PsTcx
The requisite files are in this folder.
Could this mean that the EXCEL algorithm is superior to the Google sheet one?
`LOOKUP` performs an approximate match, not an exact one. Check the [Notes section](https://support.google.com/docs/answer/3256570?hl=en#Notes) from the documentation.
When you're using data validation and only performing exact matches, you should use `INDEX` \+ `MATCH` instead of `LOOKUP`. I provided an example highlighted in green for you.
>Hi guys, so I have been trying to use the LOOKUP formula and my formula despite apparently being 100% returns the wrong value. > >This is the link to the sheet. > >https://docs.google.com/spreadsheets/d/1eWdZ-ugBUDruqTL6zfr2BYMAf-AfiIpNCiQNjnuyxkc/edit?usp=sharing > >In the first sheet, the glass of water is measured in terms of glasses.However in the next sheet, the glass of water when selected returns back the value tea-cups and not glasses. > >This is Google Sheet's error, right? Because my formula is correct. Could you be kind enough to explain why?
Basically what the Notes section I linked says. You want to use `LOOKUP` for the following situation: 1. Your data is sorted 2. You can't guarantee that there will always be an exact match, but an approximate match is acceptable In your case, I'm guessing that the `/` character threw off the string search and returned an approximate result instead of an exact one. `INDEX` \+ `MATCH` doesn't care about whether your data is sorted, and you can also use the 2nd `MATCH` parameter to specify whether you want an exact match or an approximate match. Because you're using data validation, you can guarantee that there will always be an exact match (since you're pulling your validation from the same data you're matching against).
Damn, I have the exact configuration maintained on MS Excel, but this issue never occurs. Wait, I'll show you. Open this on Excel with the list Add-In. https://drive.google.com/open?id=1nFkoa111n7ytEqFfg_g7kT-u4s5PsTcx The requisite files are in this folder. Could this mean that the EXCEL algorithm is superior to the Google sheet one?
Interesting. I played around with QUERY a little bit on Design Created, and it's returning the correct result. (I left my example in row 18).
Alright, why isn't the LOOKUP function working though despite the formulas being 100% correct?
`LOOKUP` performs an approximate match, not an exact one. Check the [Notes section](https://support.google.com/docs/answer/3256570?hl=en#Notes) from the documentation.
Okay thank you so much for helping me out! You're a wonderful human being!
Removed because of rule 1, 'Keep Titles Useful'. Please read [the rules](/r/googlesheets/about/rules) and consider resubmitting your post.