• 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 to create a table which looks up values based on rates

ExcelSeeker

New Member
I am trying to find a work around from manually filtering out the data from a sheet Everytime I have to look up available products of certain range of rates. For example, I need to look for products which are above 10.05%, what I ideally want is that the table populates itself based on available products which are >= to the rate I put in as the lookup range

Is it possible, I am using excel 2016. And yes I know I can use the filter option and look for values based on values but I want to create a new table which fetches data from another table and populates it based on the value I want to look for
 
Provide us with a sample data of what you have and mocked up solution of what you want. Upload this as a sample xlsx file.
 
For Excel 2016, try this formula solution

In B5, formula copied across right to D5 and all copied down :

=IFERROR(INDEX('Data Sheet'!A$1:A$27,AGGREGATE(15,6,ROW('Data Sheet'!$A$2:$A$27)/('Data Sheet'!$B$2:$B$27>=$C$2),ROW($A1))),"")

79581
 
Another option is to use Power Query and a parameter. I have demonstrated how this will work in the attached. Put your variable lookup in the cell identified for lookup. Either click on Refresh All on the Data Tab or click on the command button.
 

Attachments

  • PQ FilterDATA.xlsm
    31.7 KB · Views: 4
For Excel 2016, try this formula solution

In B5, formula copied across right to D5 and all copied down :

=IFERROR(INDEX('Data Sheet'!A$1:A$27,AGGREGATE(15,6,ROW('Data Sheet'!$A$2:$A$27)/('Data Sheet'!$B$2:$B$27>=$C$2),ROW($A1))),"")

View attachment 79581
This work really well, and just the thing I was looking as I have to make this for my boss and he doesn't know much about power query so this will be the ideal solution. I would really appreciate if you could explain this a bit as well.

Thanks
 
This work really well, and just the thing I was looking as I have to make this for my boss and he doesn't know much about power query so this will be the ideal solution. I would really appreciate if you could explain this a bit as well.

Thanks

Please check how to use Aggregate function as the starter, and herein one of the explanation as shown in below link:

How to use the Excel AGGREGATE function | Exceljet

Regards
 
Here the best way to learn after the given solution is to research on your own. the solution is provided by professionals, but the rest of that you will have to find out the hard way on your own. i just go thru solutions here and what is relevant to myself, i save it.
Alright
 
@shili12
Right on. Thanks for noticing that. Here is the updated Mcode that includes the value being looked up

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [Current Rate] >= Table2),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Current Rate", Percentage.Type}})
in
    #"Changed Type"
 
Back
Top