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
Filter Function in Excel 365 - Multiple Lookups
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter Function in Excel 365 - Multiple Lookups
That depends very much on how you use it. It may require complicated formulas, or custom VBA code.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Filter Function in Excel 365 - Multiple Lookups
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
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
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter Function in Excel 365 - Multiple Lookups
See the attached sample workbook. There is one array formula per row, confirmed with Ctrl+Shift+Enter.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Filter Function in Excel 365 - Multiple Lookups
I will work on it to see how it works.
Thanks,
Thanks,
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Filter Function in Excel 365 - Multiple Lookups
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,
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.
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Filter Function in Excel 365 - Multiple Lookups
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,
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,
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter Function in Excel 365 - Multiple Lookups
I am away from my computer for today and part of tomorrow. I will get back to you later.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Filter Function in Excel 365 - Multiple Lookups
Enjoy your time.
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Filter Function in Excel 365 - Multiple Lookups
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.
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.
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter Function in Excel 365 - Multiple Lookups
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.
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
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Filter Function in Excel 365 - Multiple Lookups
Thanks for all,
The link helped me a lot.
Regards,
The link helped me a lot.
Regards,