Help with INDEX / MATCH

Hi All,

I think I more or less understand INDEX / MATCH now.

But instead of matching on 1 field, I would like to match on 2 fields.

Basically, looking at the attached xlsx spreadsheet, I currently have A and B matching C (from the data sheet) and entering the correct values.

As these are surnames, there are duplicates and it is currently only matching on the first surname and not the remainder.

So I would like to match it against C and D - So only complete A and B if C and D matches.

Then secondly, you will see I have made drop down list from the data on the data sheet (C and D) and D is populated (sort of) only with matching data from C. But this is not working correctly for duplicate surnames.

If someone can please point me in the right direction or if someone can possibly assist me to get this sheet working correctly, I would really, really appreciate it.

Thanks so much.

Jakez

Does this help?

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....?!?!?!

Originally Posted by dewilk

Does this help?

Woooohooooo !!! It is perfect for the Cost Centre and Employee nr part.

Now I just need to get the 2nd drop down (Name) work based on what you have sent me.

I just need to decrypt your formula... haha...

Do you have some quick advice on this part?


Thanks so much!!!

Originally Posted by dewilk

This part
INDEX(DATA!$C$2:$C$35&DATA!$D$2:$D$35=$C2&$D2,)
is taking two ranges from column C and D from Data sheet and by comparing with Surname and Firstname returning range of TRUE/FALSE statements
then
MATCH(TRUE,INDEX(DATA!$C$2:$C$35&DATA!$D$2:$D$35=$C2&$D2,),0))
will find the position in the array
then
INDEX(DATA!A$2:A$35,MATCH(TRUE,INDEX(DATA!$C$2:$C$35&DATA!$D$2:$D$35=$C2&$D2,),0))

Returns the corresponding cell from column A or B
.
The IFERROR function returns “" if there is no match.
Use Evaluate Formula tool from Excel Option to see what's happening.

Hope that helps.

Thanks for the explanation.

I have entered the following in Name Manager as test:

=INDEX(DATA!$A$2:$D$50,MATCH(Sheet1!$C5,DATA!$C$2:$C$35,TRUE),4)

And then created a Data Validation List with Source: =test.

This seems to almost work.

If I have a non-duplicate surname, it populates D with the name. If I have a duplicate surname, it gives me a list with only 1 more option of a name.

Can you please help with my formula, as I am goin nuts.

As you can see, I dont really do Excel formulas on a regular basis and when I do, it's mostly little IF statements.

Today was my first introduction to INDEX/MATCH.

Thanks so much!

Originally Posted by icestationzbra

which one is it?

anyway, here is a similar approach, with dynamically extending ranges.

Hahaha...

Well, today was my first introduction and I understand the basics, more or less, I think.

But obviously I have a bit to go still...

Thanks for the file, I will test and revert.

Thanks

Originally Posted by icestationzbra

which one is it?

anyway, here is a similar approach, with dynamically extending ranges.

I had a look, but it seems that it populates the First Name drop down with all data in the DATA Sheet D coulomb and not just where it is matching the Surname.

My aim is to only populate the Full Name drop down with Names where the Surnames are the same.

So in this spreadsheet, if the Surname selected is Johnson, only display Susan as a selectable name, but if the Surname is Peters, then display Dina, Donna and Lewis as a selectable option.

Thanks again to everybody for the assistance.

Originally Posted by icestationzbra

see attached - it has a caveat!

Thanks so much!

Busy driving home. I'll check it when I'm at home.

Thanks again!

To add a point DATA is not defined

Thanks to everybody for the advice and examples and assistance.

I really appreciate it.

It is working very well. Exactly what I needed!

Now I just need to get it build into my main spreadsheet.

Cheers

What is ....

... working very well. Exactly what I needed!

...

Originally Posted by Marcol

What is .... ...

The spreadsheet you sent me is working 100% and the one icestationzbra sent me is also working 100%.

Pulling the correct data and populating the First Names drop down list correctly based on the Surname.

Thanks again.