I have data:
Worksheet 1
Name dept group host
A 1 A K
A 1 B F
A 2 A G
C 1 B D
D 2 C F

Worksheet 2
Name dept group host
A 1 A


Is there a auto lookup formula where I can pull data from worksheet 1 column
"host" to another worksheet with the Name, dept, group conditions met.
Eg Name =A, dept=1, and group=A. It will lookup from the table and return
host k.

One way
In Sheet2,
In D2, normal ENTER to confirm:
=INDEX(Sheet1!D$2:D$6,MATCH(1,INDEX((Sheet1!A$2:A$ 6=A2)*(Sheet1!B$2:B$6=B2)*(Sheet1!C$2:C$6=C2),),0) )
Copy down. Adjust the ranges to suit the actual extents of your source data.
Modify easily to either remove or to add-on criteria bits as needed.
Success? Hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"P2000" wrote:
I have data:
Worksheet 1
Name dept group host
A 1 A K
A 1 B F
A 2 A G
C 1 B D
D 2 C F

Worksheet 2
Name dept group host
A 1 A


Is there a auto lookup formula where I can pull data from worksheet 1 column
"host" to another worksheet with the Name, dept, group conditions met.
Eg Name =A, dept=1, and group=A. It will lookup from the table and return
host k.

Thanks it work perfectly, but can we return a "Nil" message in the host
column if the index is unable to satisify any one of the name, dept, group.
Currently I got the error #N/A if any of the group is not met.

"Max" wrote:

One way
In Sheet2,
In D2, normal ENTER to confirm:
=INDEX(Sheet1!D$2:D$6,MATCH(1,INDEX((Sheet1!A$2:A$ 6=A2)*(Sheet1!B$2:B$6=B2)*(Sheet1!C$2:C$6=C2),),0) )
Copy down. Adjust the ranges to suit the actual extents of your source data.
Modify easily to either remove or to add-on criteria bits as needed.
Success? Hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"P2000" wrote:
I have data:
Worksheet 1
Name dept group host
A 1 A K
A 1 B F
A 2 A G
C 1 B D
D 2 C F

Worksheet 2
Name dept group host
A 1 A


Is there a auto lookup formula where I can pull data from worksheet 1 column
"host" to another worksheet with the Name, dept, group conditions met.
Eg Name =A, dept=1, and group=A. It will lookup from the table and return
host k.

Indicatively, use an IF(ISNA error trap on the MATCH bit, viz.:
=IF(ISNA(MATCH(..)),"Nil",INDEX(..))

In D2, copied down:
=IF(ISNA(MATCH(1,INDEX((Sheet1!A$2:A$6=A2)*(Sheet1 !B$2:B$6=B2)*(Sheet1!C$2:C$6=C2),),0)),"Nil",INDEX (Sheet1!D$2:D$6,MATCH(1,INDEX((Sheet1!A$2:A$6=A2)* (Sheet1!B$2:B$6=B2)*(Sheet1!C$2:C$6=C2),),0)))
voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"P2000" wrote:
Thanks it work perfectly, but can we return a "Nil" message in the host
column if the index is unable to satisify any one of the name, dept, group.
Currently I got the error #N/A if any of the group is not met.

Got it! Thank you.

"Max" wrote:

Indicatively, use an IF(ISNA error trap on the MATCH bit, viz.:
=IF(ISNA(MATCH(..)),"Nil",INDEX(..))

In D2, copied down:
=IF(ISNA(MATCH(1,INDEX((Sheet1!A$2:A$6=A2)*(Sheet1 !B$2:B$6=B2)*(Sheet1!C$2:C$6=C2),),0)),"Nil",INDEX (Sheet1!D$2:D$6,MATCH(1,INDEX((Sheet1!A$2:A$6=A2)* (Sheet1!B$2:B$6=B2)*(Sheet1!C$2:C$6=C2),),0)))
voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"P2000" wrote:
Thanks it work perfectly, but can we return a "Nil" message in the host
column if the index is unable to satisify any one of the name, dept, group.
Currently I got the error #N/A if any of the group is not met.

Welcome, good to hear
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"P2000" wrote in message
...
Got it! Thank you.