2 Reference Lookup

[hyunajensen]

This should be fairly easy...I have this huge spreadsheet that I want to reference from. I want a formula that will return a "name" if "organization" and "job code" specifics are met. Ex. If Organization = Accounting and Job Code = AC4 then the response would be "Matt"

Name Organization Job Code
Bill Accounting AC1
Anne Sales SA1
Bob GA GA1
Sven Accounting AC2
Jake HR HR1
Matt Accounting AC4

 

[Krishnakumar]

Hi hyunajensen,

Welcome to MrExcel!!

Book1
	A	B	C	D	E	F	G
1	Name	Organization	Job Code		Organization	Job Code	Name
2	Bill	Accounting	AC1		Accounting	AC4	Matt
3	Anne	Sales	SA1
4	Bob	GA	GA1
5	Sven	Accounting	AC2
6	Jake	HR	HR1
7	Matt	Accounting	AC4
Sheet1

Formula in G2,

=DGET(A1:C7,G1,E1:F2)

HTH

 

[Richard Schollar]

A couple of other possibilities using Kris' table:

=INDEX(A2:A7,MATCH(1,INDEX((B2:B7=E2)*(C2:C7=F2),0),0))

or

=LOOKUP(2,1/((B2:B7=E2)*(C2:C7=F2)),A2:A7)