This one is similar to http://xlfxs.com/sort-list-using-functions/ where we use some controls here that make it more user-friendly.
Category: VLOOKUP
Searching table in 2 dimensions.
Using VLOOKUP + MATCH (HLOOKUP + MATCH, OFFSET + 2 MATCHes or INDEX + 2 MATCHes) to search a table in both axis.
This post has been sitting for a while in my archive, waiting for me to get some time to polish and post.
Skills Grid
A small Excel file to show how we can create a chart-like Excel sheet
Used in my Resume to show different skill sets and the level of expertise in each
New function IFERROR, finally
We have already gotten used to use VLOOKUP, MATCH and other functions that generates errors inside an IF
But, we do not need that anymore
We can use the new guy, IFERROR instead, which will show the result if it did not generate error, otherwise will show something else if it resulted an error.
VLookup
I have been asked for a while now to just put a simple VLookup function, here it is, I know this might be stupid to post it here, but we need to fill requests from all types of visitors.
Vlookup is the most famous function in Excel, some people actually refer to Excel as VLookup.
Even though I do not like that fact, especially when we have other powerful functions like INDEX, OFFSET, INDIRECT and others.
So, what is VLOOKUP?
VLookup is going to search for an item in the left column of your table (or selection), as in the screenshot below, then bring me back the value in the same row where it find that item from another column.
The Green box (Lookup_Value) is what the function will search for, and the vertical green box is where, this is because we selected the range (Table_Array) in Blue as the area of D:G, so VLookup will search in D, this is the V part in VLOOKUP, we should also expect to see HLookup.
Means if item is not found in column D, VLookup will return as #N/A error