» Retrieving the Entire Row of a Matched Value
CATEGORY - Lookup Formulas
VERSION - All Microsoft Excel Versions
The range A1:E6 contains ID, name, gender, and age data for a group of people.
We want to lookup the data range for each ID listed in column G and retrieve all data from the matching row.
Solution 1:
Use the VLOOKUP function as shown in the following Array formula:
{=VLOOKUP(G2,$A$2:$E$6,{2,3,4,5},FALSE)}
Select range H2:K2 and enter the above Array formula.
Then, copy/paste the formula from cells H2:K2 to H3:K4.
To apply Array formula:
Select the cell, press
Solution 2:
Use the INDEX and MATCH functions as shown in the following formula in cell H7:
=INDEX(B$2:B$6,MATCH($G2,$A$2:$A$6,0),1)
Copy the formula and paste it to all cells in the range H7:K9.
Book Store:
Recommended Books:
- The Total Money Makeover. : A Proven Plan for Financial Fitness
- Finance and Accounting for Nonfinancial Managers
- The Complete Book of Business Plans: Simple Steps to Writing a Powerful Business Plan (Small Business Sourcebooks)
- H&R Block's Just Plain Smart(tm) Tax Planning Advisor: A year-round approach to lowering your taxes this year, next year and beyond
- Managerial Accounting: Tools for Business Decision Making, WebCT, 2nd Edition
- The McGraw-Hill 36-Hour Course in Finance for Nonfinancial Managers
[SOLVED] Retrieving the Entire Row of a Matched Value
marlea
If the lookup value is repeated several times in a column, how do you use VLOOKUP to return data from ALL of the corresponding rows?
