+ Reply to Thread
Results 1 to 5 of 5

Filtered lookup with multi-column data return

  1. #1
    Registered User
    Join Date
    01-28-2021
    Location
    Florida
    MS-Off Ver
    Microsoft Office 365
    Posts
    18

    Filtered lookup with multi-column data return

    I have a spreadsheet that where I input data for pH and EC (electrical conductivity) readings for potted plants. The CropData tab has all of the raw data. On the PTDash tab, I want to be able to select the year, week number, zone and crop via drop down menus (which I already have set up) and have it return the data for those filtered items in one table.

    I'm at a loss as to whether to start with a XLOOKUP formula or a FILTER formula (or something completely different) and how best to get it to display the corresponding data in each column.

    I also need it to average the pH and the EC numbers in the space provided at the top. I think I can figure that out once I have the rest set up, but assistance on that would be helpful as well.

    Sample spreadsheet is attached. There are a few hidden tabs in the attached, but I think I have unhidden all of the ones relevant to this dashboard that I'm trying to set up. Please let me know if you have any questions.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Filtered lookup with multi-column data return

    Try

    in A8

    =INDEX(CropData!A$2:A$600,AGGREGATE(15,6,ROW($A$1:$A$600)/((CropData!$B$2:$B$600=$C$2)*(CropData!$C$2:$C$600=$C$3)*(CropData!$D$2:$D$600=$C$4)*(CropData!$F$2:$F$600=$E$2)),ROWS($1:1)))


    Copy across and change highlighted range as required.

    And please update your profile as you are on a later version of Excel than 2012 (?)

  3. #3
    Registered User
    Join Date
    01-28-2021
    Location
    Florida
    MS-Off Ver
    Microsoft Office 365
    Posts
    18

    Re: Filtered lookup with multi-column data return

    Thanks for getting back so quickly! I copied across and made the updates (see attached), and it did pull in the data for the Calathea, but when I use the drop down menus at the top to select different crops, weeks or zones, the list doesn't update. I tried "refresh all" but nothing happened.

    Is there a way to have it automatically size the results table? The initial table had two blank rows, and only came back with 2 results when there were several more in the list. I expanded the table and it comes back with #NUM! in the blank rows. I know I can edit to leave blank if no additional results are found but didn't know if there was a way to have the table autofit to the results.

    Also, I updated my profile. I have Microsoft Office 365.

    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Filtered lookup with multi-column data return

    PTDashboard


    The column labels must be the same in the PTDashboard and CropData sheets.


    A8=IFERROR(INDEX(INDEX(CropData!$A$2:$V$800,,MATCH(PTDashboard!A$7,CropData!$A$1:$V$1,0)),AGGREGATE(15,6,ROW(CropData!A$2:A$800)-ROW(CropData!A$2)+1/(CropData!$B$2:$B$800=PTDashboard!$C$2)/(CropData!$C$2:$C$800=PTDashboard!$C$3)/(CropData!$D$2:$D$800=PTDashboard!$C$4)/(CropData!$F$2:$F$800=PTDashboard!$E$2),ROWS(CropData!$A$2:CropData!A2))),"")

    COpy across and down

  5. #5
    Registered User
    Join Date
    01-28-2021
    Location
    Florida
    MS-Off Ver
    Microsoft Office 365
    Posts
    18

    Re: Filtered lookup with multi-column data return

    Thank you, this formula worked to update the information when different selections are made.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Lookup text over multi column and row array to return row number
    By benoj2005 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-19-2021, 06:32 PM
  2. Lookup to return Column Header when column data is not 0
    By ec4excel in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-25-2019, 09:14 AM
  3. Lookup and return multi data
    By excelju in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-13-2016, 04:42 PM
  4. Replies: 5
    Last Post: 09-24-2015, 03:15 PM
  5. [SOLVED] Formula to match data ( multi column) in different sheets and return matching values
    By kangyao in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-17-2013, 08:49 PM
  6. [SOLVED] Formula to match data in different sheets ( multi column) and return matching values
    By kangyao in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-11-2013, 07:38 PM
  7. Lookup filtered field to return desired result
    By dave2297 in forum Excel General
    Replies: 1
    Last Post: 02-17-2011, 04:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1