Finding a name, then returning adjacent cell values

Hi, I'm hoping someone can help me.

I need to match up some names to a master list, and collect the details and put them into another cell. My test spreadsheet is Charity Day.xlsx.

Basically, I need to match the name under the blue cells, to the name in columns A, B and C.
Then once found, I need to get their corresponding information and place them into the cells under the green headings.

I've done the first one manually as an example.

Any help would be fantastic! It doesn't matter if its a formula or macro. The version of excel this needs to work in will be 2003.

Many thanks!!

In cell K2 try:

=INDEX(D:D,MAX(INDEX(ROW($A$1:$A$1000)*($A$1:$A$1000=$I2)*(OFFSET($B$1:$B$1000,0,--(ISNA(MATCH($J2,$B:$B,0))))=$J2),0)))

And copy across and down.

Backup data, and try this macro.

Please Login or Register  to view this content.

Originally Posted by dilipandey

Hi Drone..

See the attached file where I have seperated the last name based on availabiltiy and used index - match function to achieve the desired results. thanks.

DILIPandey,

This works perfectly! Thanks for taking the time to help

Just so I can learn and better understand the functions you've used, could I trouble you to explain/confirm them at all?

=IF(ISBLANK(C2),B2,C2) ... If C2 is blank, then show B2, if not then show C2
=INDEX($A$1:$I$28,MATCH($J2&$K2,$A$1:$A$28&$D$1:$D$28,0),COLUMN(E$1)) ...
> Index A1 to I28
> Match J2 and K2 with A1 through A28 and D1 through D28 ... What does the last 0 do?
> If TRUE, then return result in E1
> Entries not matching, or not found, will return with error.

Many thanks!!

Originally Posted by Andrew-R

In cell K2 try:

=INDEX(D:D,MAX(INDEX(ROW($A$1:$A$1000)*($A$1:$A$1000=$I2)*(OFFSET($B$1:$B$1000,0,--(ISNA(MATCH($J2,$B:$B,0))))=$J2),0)))

And copy across and down.

Hey Andrew,

Cheers for this. When this is applied to Bev Pugh, the data still returns a result when it should be blank (she doesn't exist in the master list). Was there something else I needed to do for this type of formula to work?

Thanks!

Originally Posted by StevenM

Backup data, and try this macro.

Please Login or Register  to view this content.

Hey Steven,

Brilliant! I'd love to understand this better. I have a rough idea on what you're telling excel to do, but most of it is still over my head at this stage, and I think it's a simple matter of understanding the logic behind the commands.

What purpose does the Rows.Count, "x' achieve, and how is this generally used?
I imagine .End(xlUp).Row is looking for the last line of data?

I don't understand what part a long integer would play? (ie declaring i and j as long). I thought that integers only relate to numbers, and not text, so clearly I don't have a clue on how to use integers properly lol

Thank for taking the time to write this, looks pretty complex, so very much appreciated!

Cheers!

Originally Posted by scottylad2

This will do it, needs to be entered with Ctrl + Shift + Enter, not just enter. Probably not the most elegant but hey ho

Hiya Scotty,

This looks like it's working, but I have no clue on how you arrived at it lol. No offense to you, it's purely my lack of know-how on excel.

Please Login or Register  to view this content.

Also, when you say it needs to be entered using Ctrl + Shift + Enter, what difference does this make, and why is it needed?

This one looks way more complicated than the other solutions, but i am still interested in understanding how you did it. If you don't mind, you can PM me with the logic behind it

Thanks for taking the time to help.

Cheers!

Originally Posted by AlvaroSiza

Ctrl + Shift + Enter is used to designate an array. Since you appear eager to learn, I would suggest digesting the following:

http://office.microsoft.com/en-us/ex...001087290.aspx

Thanks Alvaro.

Will definitely set aside some time to go through some docs - there's just so many available, all explaining them in different ways
I've never been really good at learning through textbooks, I pick things up much faster when actually getting right into it and learn as I go.

Though having a solid foundation definitely goes a long way!

Cheers.