Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Look up Function

Posted on 2014-03-23
5
Medium Priority
?
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

721 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