Convert name and surname in to ID

[bkobal]

Hi

On the left side I have a table with names, surnames and associated ID.

I would like to enter the name (H3) and surname (I3) and the function would return an appropriate ID (J3). See picture.

Please Help.

Thanks!

 

[barry houdini]

Hello bkobal, welcome to MrExcel,

Try this formula in J3

=LOOKUP(2,1/(D2:D6=H3)/(E2:E6=I3),F2:F6)

 

[scottylad2]

Excel Workbook
	C	D	E	F	G	H	I
22	name	surname	ID		name	surname	ID
23	Bob	Smith	aaaaa		Bob	Brown	ddddd
24	Alice	johnson	bbbbbbb
25	Will	Williams	ccccc
26	Bob	Brown	ddddd
27	Cate	Jones	eeeeeee
Sheet1

 

[scottylad2]

adding the If and Or onto the front will avoid an error message appearing if you inadvertently place only 1 name in the names box

=IF(OR(G23="",H23=""),"",INDEX(E23:E27,MATCH(G23&H23,INDEX(C23:C27&D23:D27,),0)))

 

[bkobal]

Thanks!

Now it look so simple

 

[machopicho]

=sumproduct(--(d1:d5=h3),--(e1:e5=i3),(f1:f5))

 

[bkobal]

If there in no mach both functions return #N/A.

In that case i would like, that function returns "no mach" for example. How can i do that?

 

[barry houdini]

You can modify my suggestion like this

=LOOKUP("zzz",IF({1,0},"No Match",LOOKUP(2,1/(D2:D6=H3)/(E2:E6=I3),F2:F6)))

 

[scottylad2]

Doing this from my phone so not able to post a proper formula, if you use excel 2007, use =IFERROR((My Formula Here),"No Match")

 

[bkobal]

=IFERROR((IF(OR(H3="";I3="");"no data";(LOOKUP(2;1/(D2:D6=H3)/(E2:E6=I3);F2:F6))));"No Match")

This formula works OK for me. Thanks for hellp.