Pulling data from MenaData and BioData in result sheet based on employee id

[aligahk06]

Dear all,
I want to pull data from menaData sheet in Result sheet for employee code 24576, the return data correspond to date and employee id and return value from bioData sheet i.e b4, c4 etc if date and employee id match...
2. likewise pull data from bioData sheet in result sheet for employee code 24576, based on corresponding date and return timetable data in corresponding cell.i.e C5 , D5, E5 from bioData and so on...

Attached sheet...
Rgds,

aligahk06

[Kenneth Hobs]

For (1) I can lookup by ID but by date, I don't know what you mean. In C4: =VLOOKUP($B4,MenaData!$A$4:$AF$19,COLUMN()-1)
and drag right.

For (2), I think you want column E (TimeTable). I can do it in a maco. I will research a formula double lookup method.

[p45cal]

Could you fill in some sample data in the Result sheet for one date; for example fill in cells G4 and G5 (20 Aug 2019) of the Results sheet with the data you want from employee code 24576 both for Mena~ and BioData, then it will be really clear what you want to see.
For data from the MenaData sheet it's straightforward because there's only one cell to copy from, the formula in cell C4 of the Results sheet could be:

=INDEX(MenaData!$A$1:$AF$100,MATCH($B4,MenaData!$A$1:$A$100,0),MATCH(C$3,MenaData!$A$3:$AZ$3,0))

and copied to the right in that row.
However, for the BioData row (row 5) on the Results sheet, when you say:

bioData sheet i.e b4, c4 etc

phrases like 'etc' are difficult to interpret, and there's only one cell for this multi-cell BioData to fit into. So I'm particularly interested in what you want to put in cell G5 of the Results sheet.

For Kenneth Hobs' suggestion that you want just the TimeTable column (E) from the BioData sheet then you could use in cell C5 a formula such as:

=INDEX(BioData!$E$1:$E$10000,MATCH(Result!$B5 & "|" & Result!C$3,BioData!$A$1:$A$10000 & "|" & DATEVALUE(BioData!$B$1:$B$10000),0))

which needs Array-Entering which means committing the formula to the sheet with Ctrl+Shift+Enter, not just Enter. You can copy this to the right across the row too.
Note that that formula has a DateValue function in it because the dates in your BioData sheet column B are not proper dates, just text that look like dates!

[Kenneth Hobs]

For (2):

1. Make the biodata column B values into date values. They are strings now. You can use another column with this in row 2 and fill down.
=DATE(RIGHT(B2,4),MID(B2,5,2),MID(B2,2,2))

2. Insert a column before column E in biodate. Formula to fill down.
=A2&"|"&B2
a. Hide the new column if you like.

3. In result sheet cell c5 and fill right: =VLOOKUP($B5&"|"&C$3,BioData!$E$2:$F$4785,2)

[Kenneth Hobs]

cross-posted
https://www.msofficeforums.com/excel...eet-based.html

[p45cal]

cross-posted
https://www.msofficeforums.com/excel...eet-based.html

…and at Chandoo.
I now regret trying to help and won't be responding to aligahk06 on any of the forums he repeatedly cross posts to, besides, he never, ever, says thank you.

[aligahk06]

Sir,
Thank you for ur help . actually i need urgent solution that's why i was posting in different forum to get quicker solution.
I do apologise , becoz sometimes i didn't receive any feedback from amy single forum.This is the reason i post on few forums for quicker assistance.
tHANK YOU ONCE AGAIN SIR,

…and at Chandoo.
I now regret trying to help and won't be responding to aligahk06 on any of the forums he repeatedly cross posts to, besides, he never, ever, says thank you.

[Kenneth Hobs]

aligahk06, you had a solution within 6 hours. That is fast. With 100 posts, you should know this already. if you are posting to many forums, I am sure that you have seen this. If not, you should read it now. It explains common courtesy of posting links. It explains other forum etiquette. https://www.excelguru.ca/content.php?184

For forums that have it, you need to mark threads solved when solved. At least do a final post saying thanks and what solution worked or post the full solution if you tweaked it. When you get a solution, let everyone know. You need to respond and interact and help with the solution process. Early communication will help those helping you to not pursue a solution path that is off target. If someone asks a question, answer it. That is communication.

p45cal, your array method works fine if the DateValue() is removed and the date column is fixed. Maybe that is because of my regional settings of mm/dd/yyyy. That is why I showed how to make it into a date with a helper column's formula. I like your solution for (1). So, we now have 2 solutions for (1), and now 3 for (2) with this one that I did for giggles. It is an array formula too.

=VLOOKUP($B5&"|"&D$3,CHOOSE({1,2},BioData!$A$2:$A$4785 &"|"&BioData!$B$2:$B$4785,BioData!$F$2:$F$4785),2,0)

Of course for me, I would probably do it all in a macro. I was interested in this thread to practice the formula methods for this common task that I used years ago.