CODEWITHSUNDEEP
excelexcel-formula
Hodbjj
Hodbjj

Reputation: 15

Excel index/match formula issue (List)

I am looking to write an index/match formula to return a list. The value of my list is a drop down of site codes. I am looking to return a list of "employee IDs" from a separate list. writing formulas is not my strong suite at all so any help would be greatly appreciated. I have provided snips for reference. I have blanked out personal info on the roster tab for privacy purposes.
enter image description here

enter image description here

=INDEX('TOM HR Roster'!$A:$B,(MATCH($B$3,'TOM HR Roster'!$A:$A,0)),MATCH('TOM HR Roster'!B1,'TOM HR Roster'!1:1,0))

Upvotes: 0

Views: 63

Answers (1)

Jenn
Jenn

Reputation: 647

This is how you would use index/match to return the value of a column based on values from two other columns. Formatting your data as tables will make your formula more readable and less prone to errors. The tables in this example are table_Fruit (blue) and table_IndexMatch (yellow). We are looking up the values in the Fruit and Color columns of the IndexMatch table in the Fruit table and, if found, returning the corresponding ID.

enter image description here

This is the formula in cells E9:E14. This is an array formula, so after you enter the formula, with the cursor still inside the cell, Ctrl + shift + enter.

=INDEX(table_Fruit,
MATCH(1,(table_Fruit[Fruit]=[@Fruit])*(table_Fruit[Color]=[@Color]),0)
,1)

enter image description here

Upvotes: 2

Related Questions