• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help on VLOOKUP

Hello,

Need your help on Vlookup.

I am aware that the VLOOKUP will only fetch the first result from the table. Is there any way to pull the 2nd or the 3rd value from the table.

For example: In the table below, is there a way for me to get the Planned Date and Actual Visit date for a particular Hospital No. for the month of October ?
when I use =Vlookup(100,A:D,4,0) I am getting 23-May19 which was long while ago. Now if I need the date for August what should I do ? and also is there a way to get the latest Visit completed date ?

I have attached this data in excel sheet also for your easy access.
Hosptial No.Visit StatusPlanned DateActual Visit Date
100Visit Completed23-May-1923-May-19
101Visit Completed11-Jun-1901-Jun-19
102Visit Completed17-Jul-1917-Jul-19
100Visit Completed07-Aug-1907-Aug-19
101Visit Completed11-Sep-1911-Sep-19
102Visit Completed16-Sep-1916-Sep-19
100Visit Completed10-Oct-1910-Oct-19
101Visit Completed15-Oct-1915-Oct-19
102Confirmed23-Oct-1923-Oct-19
100Planned11-Nov-1911-Nov-19
101Planned03-Dec-1903-Dec-19
102Planned10-Dec-1910-Dec-19
100Planned31-Dec-1931-Dec-19
101Visit Completed18-Sep-1908-Oct-19
102Planned24-Oct-1924-Oct-19
100Planned19-Sep-1905-Nov-19
101Planned09-Oct-1906-Nov-19
102Planned18-Dec-1918-Dec-19
100Visit Completed15-Feb-1908-Feb-19
101Visit Completed26-Mar-1926-Mar-19
 

Attachments

herofox

Active Member
hi ,in this file two function to solve ,choose from it what You Want
Vlookup by Helping Column A
Code:
=IFERROR(VLOOKUP(ROW()-6,$A$2:$E$2000,5,0),"")
and Another one without any Helping (Array)Press Ctrl+Shift+Enter
Code:
IFERROR(INDEX($E:$E,SMALL(IF($I$7=$B:$B,ROW($B$2:$B$2000)-1),ROW(B1))),"")
 

Attachments

Peter Bartholomew

Well-Known Member
I will be so relieved when I can finally turn my back on traditional Excel working. I had schooled myself to commit formulas with CSE unless I has a positive reason for wishing to destroy the array structure of a solution. I accepted the fact that CSE formulas are over-rigid and somewhat user-hostile. Now with modern dynamic arrays offered by Office 365, new formulas and new solutions are available.

The list of matching records are returned by
= FILTER( Table1[Actual Visit Date], Table1[Hosptial No.]=HospitalNo )
whilst the final date is
= XLOOKUP( HospitalNo, Table1[Hosptial No.], Table1[Actual Visit Date], 0, -1 )

Each formulae could be adjusted to return entire records as opposed to simply the actual visit date.

63483
 

Attachments

GraH - Guido

Well-Known Member
Yes, already announced little over a year ago and still waiting... for them to be available for "normal" excel users :-( Getting more then frustrated by your recent posts, Peter.
 
Top