Data from multiple columns

[helenmryan11]

Hello,

I wondered if someone would be so kind to point me in the direction of the correct formula i need to be using.

I have a diary sheet where each sheet = a date (ie 22/23/24). The names of the labour are spread over 3 columns E5:G79- Then the vehicles that the labour are taking in H5:I79- Then the contract number in column j5:j79

Is it possible to create a front page that searches for a particular name(lookup or index and match through the 3 columns E,F,G) on a particular date and return their contract number (J)

Any help would be massively appreciated.

Example :So the data tab would be the front cover (2nd picture) and it will search the sheet in the correct date for the names listed in Column A if there is a match it will return the contract number in column J and do this every day matching the same date tab.

 

[AntMac]

This might take some work without the workbook. But here's what I would suggest to get started. You could use multiple .find to get the row and column where the name and the date match from the first page. Then get the value of that cell. So this would get you the row.

With Worksheets(1).Range("A1:A500")
Set c = .Find([put the cell reference of the person's name], lookin:=xlValues)
If c Is Nothing Then [choose some error handling here and maybe end]
else a = row(c.address)
end if

This would get you the column
With Worksheets(1).Range("B1:X1")
Set d = .Find([put the cell reference of the date], lookin:=xlValues)
If d Is Nothing Then [choose some error handling here and maybe end]
else a = column(d.address)
end if

Make sure you start the procedure with the cell where you want the value selected.
activecell.value = cell(a,b).value