• 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

  • example.xlsx
    10.2 KB · Views: 6
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

  • example2.xlsx
    12.7 KB · Views: 5
You can use aggregate as shown in the attached sample.
Please don't send personal messages when you post the same on the forum. Best option is to start on the forum. Always.
 

Attachments

  • last lookup.xlsx
    11.2 KB · Views: 6
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

  • Filtered list (PB).xlsx
    21.1 KB · Views: 5
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.
 
Back
Top