• 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.

INDEX MATCH TWO WAY LOOKUP ISSUE

jonastiger

Member
Hi
I´m stucked with an issue in a INDEX MATCH FORMULA.
The attached file shows my question:

I want to find a value in C4:H4 that matches a cross lookup in a column range (B5:B18) and a selection range (C5:H18).

Thank you very much in advance.

JT
 

Attachments

  • Livro1.xlsx
    11.1 KB · Views: 11
Hi
I really need help to solve this problem in my formula.
Although my efforts, I can´t solve it by my own and spent too much time googling for an answer.
So any help woud be appreciated.
Thank you very much
JT
 
Hi Bosco
Thank you very much for your great and excellent help.
It works so perfect. Fantastic combination Index Match.
JT
 
If you have a taste for exploring different solutions …

The first solution is rather like bosco's in that it first applies INDEX/MATCH to identify the row and then searches the row to find the "x" and, hence, return the fruit name.

The second is somewhat different in approach. The first step is to substitute fruit names for all "x"s using an array held in memory. The row is extracted as before but, this time, instead of searching the row, any content is simply concatenated. In this case, multiple "x"s will give rise to lists rather than single items.
 

Attachments

  • Grid Search.xlsx
    14.2 KB · Views: 12
If you have a taste for exploring different solutions …

The first solution is rather like bosco's in that it first applies INDEX/MATCH to identify the row and then searches the row to find the "x" and, hence, return the fruit name.

The second is somewhat different in approach. The first step is to substitute fruit names for all "x"s using an array held in memory. The row is extracted as before but, this time, instead of searching the row, any content is simply concatenated. In this case, multiple "x"s will give rise to lists rather than single items.
Hi Peter,

1] Your formula method 1 only work for sorted list in the range of B5:B18,

if the list is unsorted, for example: B15 value changed to 20 and J5 value changed to 20, J6 will give error (#N/A) result.

2] Your formula method 2 used a TEXTJOIN function of which only worked for Excel 2016 or above + Office 365

Regards
Bosco
 
@bosco_yip .
Hello Bosco. Yes, I fully agree. I guess there is a decision to be made about how one treats unstated requirements. The binary search has substantial advantages if the dataset approaches 100,000 rows but the dataset has to be sorted.

As you will have realised, an unsorted list would require the formulas referred to by 'activeRow' and the populated version 'populatedRow' to be changed to 'exact match'

= INDEX( selections, MATCH( required, rowLabel, 0 )
= INDEX( selectedFruit, MATCH( required, rowLabel, 0 )

by adding the third, '0', parameter. As for the version issue, I had to switch from my Office 2010 desktop to an Office 365 laptop to prove the formula. I have long had a grudge concerning the gross inadequacy of the CONCATENATE function so it was good to be able to exploit TEXTJOIN/CONCAT.

The definition of 'selectedFruit' is also a little unusual in that it combines a 2D array of criteria with a row of results to populate a 2D range of results. I am not sure that I have done that before.

Kind Regards
Peter
 
@bosco_yip .
Hello Bosco. Yes, I fully agree. I guess there is a decision to be made about how one treats unstated requirements. The binary search has substantial advantages if the dataset approaches 100,000 rows but the dataset has to be sorted.

As you will have realised, an unsorted list would require the formulas referred to by 'activeRow' and the populated version 'populatedRow' to be changed to 'exact match'

= INDEX( selections, MATCH( required, rowLabel, 0 )
= INDEX( selectedFruit, MATCH( required, rowLabel, 0 )

by adding the third, '0', parameter. As for the version issue, I had to switch from my Office 2010 desktop to an Office 365 laptop to prove the formula. I have long had a grudge concerning the gross inadequacy of the CONCATENATE function so it was good to be able to exploit TEXTJOIN/CONCAT.

The definition of 'selectedFruit' is also a little unusual in that it combines a 2D array of criteria with a row of results to populate a 2D range of results. I am not sure that I have done that before.

Kind Regards
Peter
Hi Peter,

Your formula 1 is alright, I just reminded that you must give a remark mention that "the list must sorted in suit with the formula".

Regards
Bosco
 
Back
Top