?
Solved

Excel Look up Function

Posted on 2014-03-23
5
Medium Priority
?
285 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 31

Accepted Solution

by:
gowflow earned 2000 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 31

Expert Comment

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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

I came across an unsolved Outlook issue and here is my solution.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

599 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