Solved

Excel Look up Function

Posted on 2014-03-23
5
263 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
ID: 39948312
=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
ID: 39948326
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
ID: 39948329
Elegant, gowflow!
0
 

Author Closing Comment

by:p-plater
ID: 39960226
Absolutely excellent

Top job
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39960333
your welcome. glad I could help.
gowflow
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SUMPRODUCT with criteriya 5 25
time format showing wrong 12 49
Excel callender with date slider 5 25
NEED TO UPDATE DATA IN EXCEL 18 25
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

932 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