I have Employee name In Column A and employee id in column B, In column C i have employees id's which are there in column A.
I need to run a formula to match the employee id from column C to column B and the employee name in Column A should match/sortout the output.
for EG: if Column D gives an output as 2,3,1,2 it has to mach the names accordingly. Please help with the formula.
Column A Column B(EMp Id) Column C (Employee Id)
Patrick 1 4
George 2 1
Bunny 3 2
Serge 4 33 Answers
Use a combination of MATCH() and INDEX():
In D1 enter:
=INDEX($A$1:$A$4,MATCH(C1,$B$1:$B$4,0))and copy downwards:
MATCH() finds the row and INDEX() retrieves the name.
Combination of Index & Vlookup can also finds the name:
Formula in cell
N1, fill it down:=IFERROR(INDEX(K$1:K$4,VLOOKUP(M1,L$1:L$4,1,TRUE)),"")
Adjust cell references in the formula as needed.
Or my preferred choice using VLOOKUP(): Making VLOOKUP() "look left" (which one constantly reads it cannot do...).
=VLOOKUP(C1,INDEX($A$1:$B$4,ROW($A$1:$B$4),{2,1}),2,FALSE)Standard VLOOKUP(), except for the second parameter in which one specifies the range to use.
For the range to use, INDEX() takes a range that includes the two columns of interest and extracts those two columns in the order specified by the columns parameter. In this case, that is the "array constant" {2,1} which takes the 2nd of the two columns FIRST, and then the first of the two columns SECOND, effectively creating a two column lookup table in which the lookup column comes first so the value required comes to its right.
A table of 300 columns works just as easily as the two needed are extracted (could be "{288,43}" that snags them, doesn't matter, they can be any two columns, with presumably the lower numbered one second. One might figure there's little reason to bother if it comes first but one still might like to since only the two columns are handled in the formula, not all 300 in my example.
I prefer it over Rajesh Sinha's approach as it lets VLOOKUP() look left and because it seems more straightforward.
(Given its ease of use and no funniness in its results, there's no reason on earth to use methods with CHOOSE() and its occasional weirdnesses if one is selecting more than one column.)
Of course, most things it would handle are better handled by XLOOKUP() nowadays (2021), though XLOOKUP() would not have been generally available, probably, when the question was asked:
=XLOOKUP(C1:C4,B1:B4,A1:A4)Talk about "straightforward"!
By the way, the INDEX() trick to form the lookup range works similarly with HLOOKUP(), one just uses the array constant for the rows to use, not the columns.
(Also, and this is not hard and fast, just my experience and occasional experiments when an idea comes to me, but VLOOKUP() does not seem to like BOTH the lookup value AND the column to extract the result from to use "odd" addressing. So if doing a lookup using a range of cells for the lookup value, one can only specify one column to extract results from. Or if using an array constant for the column(s) to extract results from, it refuses anything except a single value/cell for the lookup value. HOWEVER, neither of those facts affect using INDEX() in this manner.)
