Lookup match index multiple values return other columns

Hi Marty,

although you are a newbie, that doesn't stop you looking through the Forum Rules. In them you would have seen reference to duplicate posts, and why they are not tolerated, and it is clear to me that this is a duplicate post. A moderator is likely to remove one of them soon, so please remember not to do this again.

Pete

sorry I realised I'd put it in the wrong sub-class - I'm not sure of how delete the other one

thanks... not off to a good start but anybody have a formula for me???

Only moderators can delete threads. You could go back to your other thread (if that's the one you want to get rid of) and add another post to that effect. Also, you could PM one of the moderators and ask them to delete it for you (provide a link to it).

Pete

ok... got that clear....

now... can anybody help?

Well it's a bit too late for me - need to get some sleep. I'm sure someone else will be along soon.

Pete

Hi marty,

Try this formula...

=IFERROR(VLOOKUP(A1&" "&B1&" "&C1,$E$1:$E$10,1,0),IFERROR(VLOOKUP(A1&" "&C1,$E$1:$E$10,1,0),"not in list"))

Let me know if this is not what you need...

- Dennis

Sorry, Marty, here's a revision that will capture more people...

First, copy this formula in cell F1, then copy to the rest...
=LEFT(E1,SEARCH(" ",E1)-1)&" "&RIGHT(E1,LEN(E1)-SEARCH(" ",E1,SEARCH(" ",E1)+1))

Then the new formula should be the following...
=IFERROR(VLOOKUP(A1&" "&B1&" "&C1,$E$1:$E$10,1,0),IFERROR(VLOOKUP(A1&" "&C1,$E$1:$E$10,1,0),IFERROR(VLOOKUP(A1&" "&B1&" "&C1,$F$1:$F$10,1,0),IFERROR(VLOOKUP(A1&" "&C1,$F$1:$F$10,1,0),"not in list"))))

This will capture more if people have 2 or 3 names...

Misspelling such as Nicolas vs Nicholas will never be found...

Hope this helps...

- Dennis

Sorry it is the number in column D that I need - sorry I got the first post wrong

Originally Posted by vlady

How about array formula index/match

=IFERROR(INDEX($D$1:$D$10,MATCH(E1,$A$1:$A$10&" "&$B$1:$B$10&" "&$C$1:$C$10,0)),"not found") -ctrl+shift+enter

note: please test on other instances of the names, didn't test it.

This is definatley on the right track but if the middle name in column C is missing (and this is likely - because I can't get my staff to consistently put them in) then there is no result returned

WOW!

Thankyou!

Absolutley Awesome!!

thankyou to everyone who replied

Originally Posted by icestationzbra

i took the liberty to assume that only first_name and last_name need to be matched.

take a look at the attached.

Ok.... scrap my last post completly... I now have 2 new fields with pure data H and I

new sample attached

I need to match (A) first name and (C) last name - exactly with the data that is within (G) full name and return (F) phone number - this part solved - thankyou icestationzbra
If (B) middle name is present in (B) then I need to match it with what is within (G) - but won't necessarily always be in (B) even if it is in (G)

Because first and last names will have lots of matches and middle name won't always be there I also need to......

Make sure (H) suburb and (I) postcode matches with what is in (D) and (E) before returning (F) phone number


Hope this makes sense... what a headache!

Anybody????

help!

Hi marty,

Great formula given by icestation, but I personally like vlady's solution... here's a modified version... that might work for you...
=IFERROR(INDEX($F$3:$F$12,MATCH(G3&I3&J3,$A$3:$A$12&" "&$B$3:$B$12&" "&$C$3:$C$12&$D$3:$D$12&$E$3:$E$12,0)),IFERROR(INDEX($F$3:$F$12,MATCH(H3&I3&J3,$A$3:$A$12&" "&$C$3:$C$12&$D$3:$D$12&$E$3:$E$12,0)),"not found"))
ctrl-shift-enter

vlady - didn't mean to step on your work, I just wanted to give marty a solution...

Sorry, marty...

You have to add a helper column H for the formula to work (like I suggested before)... see the attached file...

sample3(1).xlsx

Fantastic help guys!!! really really appreciated...!!!! Thankyou again to everyone

See the attached