Cross Referencing Problem

[BillHassall]

I have a worksheet that every day is fillied in with a number of jobs that have been completed the previous day against a number of engineers, with each engineer having done three or four jobs the previous day, so there is one line for each completed job. The engineers name being in column D. This can be 100 to 200 jobs each day

Each engineer is seperately listed as being either an "in area" or "out of area" engineer columns H:L, there is other data recorded as well. this is a list of about 40 engineers

I am trying to add in column G a lookup that says if the engineers name in D2 equals the engineers name in H2 then input the data from L2, if the engineers name in D2 equals the name in H3 then input L3 etc all the way down the jobs that have been completed.

The only way that I can see to do is is to have a formula with about 40 IF statements in, is there an easier way to do this

Thanks

Bill

 

[yogeshmaney]

Kindly provide data sample

 

[BillHassall]

This is the job detail
<TABLE style="WIDTH: 483pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=643><COLGROUP><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 5705" width=156><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><COL style="WIDTH: 224pt; mso-width-source: userset; mso-width-alt: 10898" width=298><COL style="WIDTH: 45pt" width=60><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 117pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20 width=156> Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 97pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=129> Action</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 224pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=298> Notes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 45pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=60>In/Out</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e2; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=20>John Anderson</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>COM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>task completed by john anderson</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e2; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=20>Michael Montgomery</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>COM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>task completed by michael montgomery</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e2; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=20>Gordon Brown</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>COM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>tasl completed by gordon brown</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71> </TD></TR></TBODY></TABLE>

And this is the engineer detail

<TABLE style="WIDTH: 310pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=413><COLGROUP><COL style="WIDTH: 106pt; mso-width-source: userset; mso-width-alt: 5156" width=141><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 45pt" width=60><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 106pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20 width=141>NAME</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=100>TECHNICIAN ID</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=28>NO</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 63pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=84>NUMBER</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 45pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=60>In/Out </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e2; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=20>John Anderson</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>In</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e2; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 height=20>Steve McAleese</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>out</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #8db4e2; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=20>Jim Laing</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e2; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>In</TD></TR></TBODY></TABLE>

I want to reference enginneers In/Out details against the job that they have completed, so the first job completed by John anderseon would reference the engineer info and say that he was an "In" area engineer

 

[BillHassall]

I tried the following first and it didn't work, just input again and it now works perfectly, one of those days


=IF(ISERROR(VLOOKUP(D2,H:L,5,FALSE)),"",(VLOOKUP(D2,H:L,5,FALSE)))


Bill

 

[yogeshmaney]

Please provide with Column also What is L column and what is H. I can't guesss. Better u give a snap shot.

 

[BillHassall]

The job detail is as follows
Column D is name
Column E is Action
Column F is Notes
Column G is in/out

The engineer detail
Column H name
Column I is technician ID
Column J is No
Column K is Number
Column L is in/out

Just moved to a Webtop version of Windows 7 which doesn't allow, things like Zapgrab to work, so I am unable to paste in as a picture.

The formula I posted now works, even though it didn't earlier on

Bill

 

[yogeshmaney]

=if(iserror(index(l:l,match(d2,h:h,0),1)),"",index(l:l,match(d2,h:h,0),1))

 

[BillHassall]

Thanks, works a treat

Bill