Note1 -- When the column to match is not the first column of the table:
If the key column is not the first column of a table -- even if it is on the right side of the columns you need to return -- you can adjust the VLOOKUP with the CHOOSE function, eg:
You key is in D1
10 and you want to return corresponding the values from B1:B10:
=VLOOKUP( key_here , CHOOSE( {1,2} , D1
10 , B1:B10 ) , 2 , FALSE )
--> in the CHOOSE section is put D column first to search on it.
--> no need to touch the source data
Note2 -- Returning multiple columns of data
INDEX and Match are definitely more efficient as =INDEX( MATCH( )
, but especially if you need return values from multiple columns.
- You can use a 'dummy' column, say col A, to return the row of the match with MATCH() in the data table
- then in B,C,D... use INDEX( using value in in A) in all subsequent columns.
This way, the hard part 'search and compare' handled by the MATCH only once per row ( in col A).
Note 3:regarding the 'travel to all branches' problem -- this is a TSP problem. You can use the Excel Solver addin to solve this types of problems when the number of places to visit is small. Worth trying in your case.
Excel is an incredible piece of software.