Excel OfficeExcel comes with multiple Lookup and Reference functions that let you find matching values. The Lookup and Reference functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets
To do a two-way lookup in an Excel Table, you can use the MATCH function with a structured reference and VLOOKUP. See example below: Recall that VLOOKUP depends on the lookup value being to the left of the value being retrieved in a table. Generally, this means the lookup value will be the first value in the table…. read more »
To translate letters in a string to numbers, you can use an array formula based on the TEXTJOIN and VLOOKUP functions, with a defined translation table to provide the necessary lookups. Formula {=TEXTJOIN(“”,1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1))),xtable,2,0))} Explanation In the example shown, the formula in C5 is: {=TEXTJOIN(“”,1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1))),xtable,2,0))} where “xtable” is the named range E5:F10. Note: this is an array formula and must… read more »
This tutorial shows how to Group times into unequal buckets in Excel using the example below; Formula =VLOOKUP(time,bucket_table,column,TRUE) Explanation This formula is a great example of how you can use VLOOKUP to group data in completely custom ways. If you need to group times into buckets, but the buckets are not equal (i.e. 12 AM-7… read more »
This tutorial shows how to Map text to numbers in Excel using the example below; Formula =VLOOKUP(text,lookup_table,2,0) Explanation To map or translate text inputs to arbitrary numeric values, you can use the VLOOKUP function with a simple table. In the example, we need to map five text values (statuses) to numeric status codes as follows:… read more »
This tutorial shows how to Map inputs to arbitrary values in Excel using the example below; Formula =VLOOKUP(input,map_table,column,0) If you need to map or translate inputs to arbitrary values, you can use the VLOOKUP function. In the example, we need to map the numbers 1-6 as follows: Input Output 1 10 2 81 3 17… read more »
This tutorial shows how to Group numbers with VLOOKUP in Excel using the example below; Formula =VLOOKUP(value,group_table,column,TRUE) Explanation If you need to group by number, you can use the VLOOKUP function with a custom grouping table. This allows you to make completely custom or arbitrary groups. In the example shown, the formula in F7 is: =VLOOKUP(D5,age_table,2,TRUE) How… read more »
This tutorial shows how to Group arbitrary text values in Excel using the example below; Formula =VLOOKUP(value,table,column,0) Explanation IF you want to group or classify data based on arbitrary text values, you can use VLOOKUP instead of a long series of nested IF statements. The trick is to build a custom table that will map… read more »
This tutorial shows how to work Win loss points calculation in Excel using the example below; To assign points based on win/loss/tie results for a team, you can use a simple VLOOKUP formula, or a nested IF formula, as explained below. Formula =VLOOKUP(result,points_table,2,0) Explanation In the example shown, the formula in D5 is: =VLOOKUP(C5,points_table,2,0) How this… read more »
This tutorial shows how to calculate VLOOKUP with numbers and text in Excel using the example below; Formula =VLOOKUP(val&””,table,col,0) Explanation A common problem with VLOOKUP is a mismatch between numbers and text. Either the first column in the table contains lookup values that are numbers stored as text, or the table contains numbers, but the… read more »
This tutorial shows how to calculate VLOOKUP without #N/A error in Excel using the example below; Formula =IFERROR(VLOOKUP(value,table,2,FALSE),””) Explanation To hide the #N/A error that VLOOKUP throws when it can’t find a value, you can use the IFERROR function to catch the error and return any value you like. How the formula works When VLOOKUP… read more »