Neep help on formula to Pull based on code & falling within certain time frame

B

baldwinp

New Member
Joined
Sep 26, 2014
Messages
8
Hi I have been strugling with this, and I was hoping someone could be of help, basically on my data tab i need to pull in the right name based on the code matching from the map tab and the date falling between the start and end. For example the first name the formula would find would return Joe. I was thinking it would be done with some type of array formula, but am drawing a blank. Thank you to anyone who can help. Also this is just an example, the actual data im dealling with is much larger and I will be pulling multiple things from the map to the raw data.


Maping tab[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[/TR]
[TR]
[TD]code
[/TD]
[TD]start
[/TD]
[TD]end
[/TD]
[TD]name
[/TD]
[/TR]
[TR]
[TD]abc123
[/TD]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]11-Jan
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<TBODY>[TR]
[TD="align: right"]26-Sep</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD]pat
[/TD]
[/TR]
[TR]
[TD]abc123
[/TD]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1-Jan
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<TBODY>[TR]
[TD="align: right"]10-Jan</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD]joe
[/TD]
[/TR]
[TR]
[TD]xyz678
[/TD]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]7-Sep
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<TBODY>[TR]
[TD="align: right"]13-Sep</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD]nick
[/TD]
[/TR]
[TR]
[TD]xyz678
[/TD]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1-Aug
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<TBODY>[TR]
[TD="align: right"]6-Sep</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD]tim
[/TD]
[/TR]
</TBODY>[/TABLE]


Raw Data[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[/TR]
[TR]
[TD]code
[/TD]
[TD]date
[/TD]
[TD]name formula
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]abc123
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]5-Jan
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]abc123
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]6-Jan
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]abc123
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]25-Mar
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]xyz123
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]15-Aug
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]xyz123
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]28-Aug
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]abc123
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]10-Aug
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]xyz123
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]5-Sep
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]xyz123
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]12-Sep
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
 
Sort by date Sort by votes
<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H2</th><td style="text-align:left">=LOOKUP(<font color="Blue">9.99999999999E+307,1/(<font color="Red">(<font color="Green">$F2=$A$2:$A$5</font>)*(<font color="Green">$G2>=$B$2:$B$5</font>)*(<font color="Green">$G2<=$C$2:$C$5</font>)</font>),$D$2:$D$5</font>)</td></tr></tbody></table></td></tr></table><br />






Excel 2013
ABCDEFGH
1codestartendnameCODEDATENAME
2abc12311/01/201426/09/2014patabc12305/01/2014joe
3abc12301/01/201410/01/2014joeabc12306/01/2014joe
4xyz67807/09/201413/09/2014nickabc12325/03/2014pat
5xyz67801/08/201406/09/2014timxyz67815/08/2014tim
6xyz67828/08/2014tim
7abc12310/08/2014pat
8xyz67805/09/2014tim
9xyz67812/09/2014nick
Sheet1
 
Upvote 0
Thanks a lot this works perfectly, If you could explain it that would be great too, I have know idea why this works, but it does. Thanks agin!
 
Upvote 0

($F2=$A$2:$A$5)*($G2>=$B$2:$B$5)*($G2<=$C$2:$C$5) this is testing every condition that has to be met

given your layout, the above should either return an array of all 0s (all 3 conditions are never TRUE) or an array with a 1 and the rest 0s

if i divide 1 by this array I will get another array which is either all errors (DIV/0, divison by 0) or an array with a 1 and the rest errors

the LOOKUP will return the position of this 1, and ultimately the name associated to this position


a visual lesson about the LOOKUP function is better than 1000 words, so, if interested, watch:

https://www.youtube.com/watch?v=X23XJjasbZg&index=12&list=PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci

https://www.youtube.com/watch?v=N24cBM-CTw4

https://www.youtube.com/watch?v=aleaMFPk4-I

 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Index Match function List based on date
Replies
7
Views
420
Fluff
Fluff
F
  • Question Question
Count # of values that fall within a certain date range
Replies
1
Views
609
RobP
R
M
  • Question Question
Look up 3 criteria to give a result if date falls within a week ending period
Replies
1
Views
335
duggie33
D
C
  • Question Question
Need help with look up formula
Replies
5
Views
694
GuzmanServices
G
lost_in_the_sauce
  • Solved
How to get value with most recent date in an array
Replies
6
Views
573
Fluff
Fluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top