You are reading the article Excel Factor 16 Dynamic Lookup updated in October 2023 on the website Nhunghuounewzealand.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 Excel Factor 16 Dynamic Lookup
This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee.Words by Bryon Smedley.
Enter your email address below to download the sample workbook.
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
VLOOKUP is great for returning information from a database, but one of the limitations is that the return information is static.
What if the user wishes to look for certain data one day but different data another day? This would require either two different sets of VLOOKUP functions or the functions would need to be reprogrammed.
In the database below, the user would wish to return address information in one scenario, but return financial information in another scenario.
Suppose there are times when the user requires a mixture of the two; that would require a third set of VLOOKUP functions. This could become an ever evolving set of work.
ORIGINAL DATABASE
ADDRESS INFORMATION
FINANCIAL INFORMATION
MIXED INFORMATION
Here comesMATCH
to the rescue!!!
The MATCH function’s job is to return the relative position of data within a defined array.
The syntax for the MATCH function is:
=MATCH(Lookup_value
,Lookup_array
,Match_type
)
Lookup_value
– The value that you want to find in the list of data. This argument can be a number, text, logical value, or a cell reference.
Lookup_array
– The range of cells being searched.
Match_type
(optional) Tells Excel how to match the
Lookup_value
with values in the
Lookup_array
. Choices: -1, 0, or 1. The default value for this argument is 1.
If the Match_type = 1 or is omitted: MATCH finds the largest value that is less than or equal to the
Lookup_value
. The
Lookup_array
data must be sorted in ascending order.
If the match_type = 0: MATCH finds the first value that is exactly equal to the
Lookup_value
. The
Lookup_array
data can be sorted in any order.
If the Match_type = -1: MATCH finds the smallest value that is greater than or equal to the
Lookup_value
. The
Lookup_array
data must be sorted in descending order.
If we wish to give the user the ability to dynamically select which fields of interest to return information from, the MATCH function can examine the category for each row (or column) and use it to calculate the position of that choice in the database.
That position number can then be used for the VLOOKUP Col_Index_Num variable.
*** Remember – VLOOKUP has the following syntax: ***
Let’s look at a VLOOKUP from the static ADDRESS INFORMATION table (FYI: The above database which occupies range $A$5:$L$29 has been given a NAMED RANGE of “Database1”)
The “2” in the third variable position is telling us to return data from the 2nd relative column position from within the table (in this case, Column “B”).
We can calculate that position with the following MATCH function (FYI: The database’s header row which occupies range $A$4:$L$4 contains all of the category names and has been given a NAMED RANGE of “Categories“)
=MATCH(A2
,Categories
,0
)If we execute this function by itself, it would return “2” as an answer, since “L-Name” exists in the 2nd column of the database.
Now we’ll substitute the original “2” in the Col_Index_Num variable with the MATCH function:
=VLOOKUP(B1,Database1,MATCH(A2
,Categories
,0
),FALSE)*** IMPORTANT ***
The categories that the user types in Column “A” (in the above example) MUST match the names used in the database header row.
Let’s add some pizzazz to this processSince a requirement of the MATCH function is we use the same naming convention as the database, and the database contains all of the names, let’s use those names in a dropdown list so the user can select items with greater ease and precision.
This can be accomplished by use of the Data Validation tool.
If we define a report area large enough to display all of a record’s information (accommodate the maximum number of return items), what happens when a user does not use all of the slots?
Any place a category is not defined, a “#N/A” error message will appear. By placing all of the above VLOOKUP logic inside of an IFERROR function, we can suppress the “#N/A” error messages.
The blanks are generated by means of two double quotation marks placed side by side (no space between them).
=IFERROR(VLOOKUP(B1,Database1,MATCH(A2,Categories,0),FALSE)
,"") BONUS TRICKIf your dataset had non-user friendly headers, you could get fancy and have a table of official headings/user friendly headings and nest another VLOOKUP in the Lookup_Value variable position.
This would allow you to have understandable choices in your data validated dropdown list, but still find the correct category in the less friendly database headings.
The table above was given the name “NewHeader”
=VLOOKUP(B1,Database1,MATCH(VLOOKUP(A2,NewHeader,2,False)
,Categories,0),FALSE)Bryon is from Bristol, Tennessee and has been teaching Excel since version 7 which was included with Office 95. He is currently a Technical Training Analyst for one of the largest coal companies in the world. His key responsibility is to conduct all Microsoft Office training, but in addition he also serves as a technical consultant for any and all projects involving Microsoft Office applications.
Bryon says “Excel is by far my favorite among all the Office applications due to the almost infinite number of situations it can be used. There literally seems to be no end to its usefulness.
My favorite Excel tools are difficult to narrow down. Without getting into third party add-ins, I would have to say PivotTables (especially with the addition of Slicers. WOW! Are those things awesome!!), the Text to Columns tool (what a time saver), and Macros.”
Vote for BryonYou're reading Excel Factor 16 Dynamic Lookup
Update the detailed information about Excel Factor 16 Dynamic Lookup on the Nhunghuounewzealand.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!