VLOOKUP in Excel Tips and Tricks
Here is the list of few VLOOKUP in Excel tips to use the excel worksheet function more effectively.
1. Use named Ranges: the vlookup function with normal range variables looks very complicated. To avoid this, use named ranges. With named ranges, you can do dynamic ranges which saves lot of memory and by that, the time. You can read more dynamic ranges in excel here.
2. 2 way or conditional vlookup: The default vlookup accepts only single criteria lookup. But using the match function, you use vlookup function which looks for the given value in row as well as in columns. The syntax is same as the normal vlookup apart from inserting match function instead of index number. The match function returns the reference number of the column. To learn more on this read my post on conditional vlookup.
3. Handling error values in Excel: When the lookup value is not found, vlookup returns, #na. You can remove this by using iferror function. This will make your excel template more clean and easy to perform calculations
=IFERROR(VLOOKUP(B16,A2:C6,2,FALSE),0)
4. Use Wildcard characters for related lookup: You can use related lookup using the last argument as TRUE. but, it has a downside. The range should be in ascending order. The wildcard character will help in these situations. Below are the few examples.
=VLOOKUP(“Galaxy*”,A2:C8,3,FALSE)- returns the value from the range which starts with Galaxy. If there are multiple Galaxy are there, then it will take the first one.
=VLOOKUP(“????”,A2:C8,3,FALSE) this look for a value with the lookup value length is 4.
Similarly you can use most of the wildcard characters in vlookup also.
If you find these tips on VLOOKUP in Excel, or have any queries, feel free to leave feedback in the comment section below.
Leave a Reply