Solved

Excel Look up Function

Posted on 2014-03-23
5
260 Views
Last Modified: 2014-03-27
Hello

I need a formula to fill in the date in column B when there is a Value of 2 in the row
So Name 1 should have a Last Visit date of 03/03/2014 Name 2 02/02/2014 and Name 3 03/03/2014

The date needs to be the Date in row 2 that is above the FIRST value of 2 that is in that row.
Needs to be dynamic so I can drag it down a couple of thousand rows

Thanks
Visit-Date.xlsx
0
Comment
Question by:p-plater
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:WalkaboutTigger
Comment Utility
=if($c3=2,$c$2,if($d3=2,$d$2,if($e3=2,$e$2)))

Try this
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
Put this formula in B3 and Drag it down as much as you have data. I have set the Columns to go till Col Z but if you have more dates beyond Col Z change the Z to the last Column of data.

Put this Formula in B3 and drag it down.
=IFERROR(LARGE($C$2:$Z$2,MATCH(2,C3:Z3,0)),"")

Congratulation on your Icon Conditional formatting very neat.
gowflow
Visit-Date.xlsx
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
Comment Utility
Elegant, gowflow!
0
 

Author Closing Comment

by:p-plater
Comment Utility
Absolutely excellent

Top job
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
your welcome. glad I could help.
gowflow
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now