| Case Sensitive Reverse Lookup With Error Trap | |||||||||||||||||||
| Word | Value | Word | Value | ||||||||||||||||
| 200 | CAt | bag | come on a course and learn how its done | ||||||||||||||||
| 300 | CAT | CAT | 300 | ||||||||||||||||
| 900 | Cat | Cat | 900 | ||||||||||||||||
| 500 | cat | cat | 500 | ||||||||||||||||
| 600 | caT | caT | 600 | ||||||||||||||||
| =IFERROR(VLOOKUP($D5,IF(EXACT($D5,$B$5:$B$9),CHOOSE({1,2},$B$5:$B$9,$A$5:$A$9)),2,FALSE), "come on a course and learn how its done")} |
|||||||||||||||||||
| Please be aware that it is an array formula so you need to use CTRL + SHIFT + ENTER keyboard shortcut. That's why there are braces around the formula. They appears after you typed CTRL + SHIFT + ENTER | |||||||||||||||||||
Monday 31 July 2017
Case Sensitive Reverse Lookup With Error Trap
Case Sensitive Reverse Lookup
When you want to max out your look up skills then try this one, our lookup functions are explained on our training courses.
| Case Sensitive Reverse Lookup | ||||||||||||
| Word | Value | Word | Value | |||||||||
| 200 | CAt | CAt | 200 | |||||||||
| 300 | CAT | CAT | 300 | |||||||||
| 900 | Cat | Cat | 900 | |||||||||
| 500 | cat | cat | 500 | |||||||||
| 600 | caT | caT | 600 | |||||||||
| {=VLOOKUP($D9,IF(EXACT($D9,$B$5:$B$9),CHOOSE({1,2},$B$5:$B$9,$A$5:$A$9)),2,FALSE)} | ||||||||||||
| Please be aware that it is an array formula so you need to use CTRL + SHIFT + ENTER keyboard shortcut. That's why there are braces around the formula. They appears after you typed CTRL + SHIFT + ENTER | ||||||||||||
Case Sensitive VLookup
Here is another one of our lookup Functions, this time adding if and exact to create a case sensitive VLookup how's that for a little magic
| Case Sensitive VLookup | |||||||||
| Word | Value | Word | Value | ||||||
| CAt | 200 | CAt | 200 | ||||||
| CAT | 300 | CAT | 300 | ||||||
| Cat | 900 | Cat | 900 | ||||||
| cat | 500 | cat | 500 | ||||||
| caT | 600 | caT | 600 | ||||||
| {=VLOOKUP($D2,IF(EXACT($D2,$A$2:$A$10),$A$2:$B$10),2,0)} | |||||||||
| Please be aware that it is an array formula so you need to use CTRL + SHIFT + ENTER keyboard shortcut. That's why there are braces around the formula. They appears after you typed CTRL + SHIFT + ENTER | |||||||||
Two Ways to do a Reverse Lookup
So they told you it couldn't be done we prove them wrong not once but twice, do you have a favourite?
| Two Ways to do a reverse Lookup | ||||||||||||
| Price | Product | Product | Price | |||||||||
| £ 150.00 | Product1 | Product2 | 225 | |||||||||
| £ 225.00 | Product2 | |||||||||||
| £ 300.00 | Product3 | =INDEX($B$3:$B$10,MATCH($F$3,$C$3:$C$10,0)) | ||||||||||
| £ 375.00 | Product4 | |||||||||||
| £ 450.00 | Product5 | |||||||||||
| £ 525.00 | Product6 | |||||||||||
| £ 600.00 | Product7 | |||||||||||
| £ 675.00 | Product8 | |||||||||||
| Price | Product | Product | Price | |||||||||
| £ 150.00 | Product1 | Product2 | 225 | |||||||||
| £ 225.00 | Product2 | |||||||||||
| £ 300.00 | Product3 | =VLOOKUP(F14,CHOOSE({1,2},C14:C21,B14:B21),2,FALSE) | ||||||||||
| £ 375.00 | Product4 | |||||||||||
| £ 450.00 | Product5 | |||||||||||
| £ 525.00 | Product6 | |||||||||||
| £ 600.00 | Product7 | |||||||||||
| £ 675.00 | Product8 | |||||||||||
Subscribe to:
Posts (Atom)