Filter Function in Excel 365 - Multiple Lookups

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Filter Function in Excel 365 - Multiple Lookups

Post by BittenApple »

Hello all,

I used a filter function to bring multiple matches. That worked perfect, now I am thinking if others don't have Excel 365, they can't use my workbook.

What function do I need to use to look up values in 2 or 3 columns and bring the result back from column 4, 5?


Please advise me.

Regards,
BittenApple

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter Function in Excel 365 - Multiple Lookups

Post by HansV »

That depends very much on how you use it. It may require complicated formulas, or custom VBA code.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Filter Function in Excel 365 - Multiple Lookups

Post by BittenApple »

Hans,

Thanks for the response!!

Can I go with a formulas?

I already wrote a couple of them, but the rows are not updated:

I used this array function:
=Index(sheet2!$A$2: $G$200, Match(sheet1!$A$1&Sheet1$A$2,Sheet2!$B$2:Sheet2!$B$200 & Sheet2!$C$2:Sheet2!$C:$200,0),4)
It doesn't get updated for rows.

Then I used this:
{=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0))}

again, that both function copies the value down the column for all rows.

Any tips?

Thanks,
Bita

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter Function in Excel 365 - Multiple Lookups

Post by HansV »

See the attached sample workbook. There is one array formula per row, confirmed with Ctrl+Shift+Enter.

MultiResults.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Filter Function in Excel 365 - Multiple Lookups

Post by BittenApple »

I will work on it to see how it works.
Thanks,

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Filter Function in Excel 365 - Multiple Lookups

Post by BittenApple »

Hello Hans,

Thanks for this great response.
I understood it.

How does this part below bring the correct row number?

SMALL(IF((Sheet2!$A$2:$A$200=$A$2)*(Sheet2!$B$2:$B$200=$B$2),ROW(Sheet2!$A$2:$A$200)-MIN(ROW(Sheet2!$A$2:$A$200)+1),ROWS($A$5:$A22)),)

Can you please kindly explain how the values inside of array row by row are processed? (I want to get a mental picture).

Thanks,
Last edited by BittenApple on 02 Jun 2021, 01:31, edited 1 time in total.

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Filter Function in Excel 365 - Multiple Lookups

Post by BittenApple »

Hello Hans,
The function worked. I select the range and enter the formula in the first cell, it pulled the data but the first row values are repeated for all rows.
I had this problem with other array formulas, the first row is getting repeated. I don't know what to do.

Any tips,

Regards,

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter Function in Excel 365 - Multiple Lookups

Post by HansV »

I am away from my computer for today and part of tomorrow. I will get back to you later.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Filter Function in Excel 365 - Multiple Lookups

Post by BittenApple »

:thankyou: :thankyou:
Enjoy your time.

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Filter Function in Excel 365 - Multiple Lookups

Post by BittenApple »

Hello Hans,
I am using office 365, these are my experience:

First, for arrays function, there is no need to select the whole area and enter the formula and then hit ctrl+Shift+enter, simply enter the function in one cell and then hit enter only, data spills across and down the column.

Second, I create table from my range, when I want to use that table in my functions, if I want to see the columns for that table,I have to go to the sheet and keep the mouse pointer until it changes to a thick arrow and then select the table. If I enter the name of the table in the function without going to the sheet to select the table, I wouldn't be able to see the columns.

That is my experience only although others might not agree with it.

Regards,

P.s. The function worked perfect.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter Function in Excel 365 - Multiple Lookups

Post by HansV »

Excel in Microsoft 365 has a whole new way to deal with formulas that can return multiple values: they will automatically fill the cells needed and only those cells. This is called "spilling".
There is no way to completely emulate this in older versions of Excel, the "spilling" mechanism simply doesn't exist there. We can use IFERROR to hide "surplus" values, but that is not a real equivalent.
See Extract multiple matches into separate rows for an explanation of the formula in the workbook that I attached.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Filter Function in Excel 365 - Multiple Lookups

Post by BittenApple »

Thanks for all,
The link helped me a lot.
Regards,