Help using Match and Index... I think

**thisiscrazy** · 11-02-2011, 05:59 PM

I am hoping to create a formula that looks at a table of employee names and the program they are charged to and displays the appropriate employee name based on the program number

I have attached a workbook, because I am positive that I am not being concise or clear enough.

I appreciate the time and guidance.

**thisiscrazy** · 11-02-2011, 06:33 PM

I updated the spreadsheet to show exactly what I am doing with some additional formulas and data.

I thought it may help.

Thanks in advance

**arlu1201** · 11-03-2011, 03:03 AM

I am still not able to understand what output you want. Maybe you can include a "After" sheet to try and explain what you want to be displayed.

**thisiscrazy** · 11-03-2011, 09:34 AM

I apologize for not being clear.

The output wanted is the appropriate employee name in column A who is paid from program 2100 or program 5100 as laid out on the salary breakdown worksheet.

Currently if I type in their name exactly as it is on the other worksheet their salary and how much they are charged to that specific program are outputted to the right.

Thanks!

**NBVC** · 11-03-2011, 09:54 AM

Try this formula in A4:

=IFERROR(INDEX(Names,SMALL(IF(ISNUMBER(INDEX('Salary Breakdown'!$D$5:$Q$9,0,MATCH(LOOKUP(9.99999999999999E+307,$A$3:$A3),'Salary Breakdown'!$D$3:$Q$3,0))),ROW(Names)-ROW(INDEX(Names,1,0))+1),ROWS(INDEX($A$3:$A3,MATCH(9.999999999999E+307,$A$3:$A3)):A3))),"")

confirmed with CTRL+SHIFT+ENTER and copied down to A7.

Then copy A4 and paste to A9 and copy down, then paste to A13 and copy down, etc....

**thisiscrazy** · 11-18-2011, 10:35 PM

Sorry for the long time with no response. We had a family emergency and I was out for awhile.

Thanks for the formula! That worked perfectly.

I couldn't tweak it to work in my actual spreadsheet for some reason (well, user error actually)

But it works perfectly and I appreciate it.

Actually I think you have built most of this thing over the years.