Help with INDEX / MATCH

Originally Posted by vlady

Hello
how about
Enter Employee nr then from this the index/match will be populating the Cost center,surnames names and first name

enter Employee Nr instead of using surnames...after all i think all employee nr do have duplicates...

INDEX(DATA!$A$2:$D$50,MATCH(employee NR,DATA!$B$2:$B$35,FALSE),4)

Thanks for the response.

The problem is that the employee numbers are not easy to find in this case.

The data on the data sheet is imported from a csv.

And the data capturing is happing on sheet1 from hard copy registers, which are completed with date, name, surname, signature - so the employee number is not available on the source documents.

This is the reason I would like to get the data capturer to enter the surname, then have the drop down with available people matching that surname and then based on the name selected, fills in the employee number and cost centre.

Any ideas?

I have been playing with what I found here: http://support.microsoft.com/kb/214142/EN-US

But thusfar I am unable to get it to work correctly.

I have tried this:

=INDEX(DATA!$A$2:$D$50,MATCH($C2&$D2,DATA!$C$2:$C$35&DATA!$D$2:$D$35,0),2)

But it just gives me a #VALUE! or #REF! as a result...

Originally Posted by jakezp

=INDEX(DATA!$A$2:$D$50,MATCH($C2&$D2,DATA!$C$2:$C$35&DATA!$D$2:$D$35,0),2)

This works if I enter the formula and then push CTRL+SHIFT+ENTER.... Weird....?!?!?!