• 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 or VBA

Dear All,

I have a sample file which I am attaching herewith. My query is below.

There are 2 sheets named Matrix and Data in the file. 1st sheet named Matrix has Area, Building name along with range of unit sizes (Studio, 1 Bed, 2 Bed etc.). 2nd sheet named Data has the Area, Building name along with unit sizes of buildings. Accordingly, I need to retrieve the Bed levels for those based on the matrix. For eg: Area name is M & Building name is m and the unit size is 1600, I need to check in matrix sheet whether 1600 falls in which range whether it is 2 bed or 3 bed and I need to populate the bed number in the column named "Beds" of Data sheet.

I was trying to use Index & Match. However, the column section of the formula has to be dynamic is what I feel. The row no. retrieved has to be used as the array for match formula in column section. Tried to use concatenate, but not working.

Could anyone please help quickly. Its on urgent basis.

Thank you very much
 

Attachments

  • Sample.xlsx
    15.1 KB · Views: 2
Thank you very much Bosco. I request you it would be awesome if you could explain the formula for my learning please.

Thanks again
Hi,

Explanation based on C2 formula :

=IFERROR(IF(LOOKUP(9^9,INDEX(Matrix!$C$3:$N$28,MATCH(1,INDEX((Matrix!A$3:A$28=A2)*(Matrix!B$3:B$28=B2),0),0),0))>E2,LOOKUP(2,INDEX(1/((E2>=INDEX(Matrix!$C$3:$N$28,MATCH(1,INDEX((Matrix!A$3:A$28=A2)*(Matrix!B$3:B$28=B2),0),0),0))=TRUE),0),Matrix!$C$2:$N$2),"Not in list"),"Not in list")

The above formula basically combined with Conditional Lookup, Last Cell Lookup and Conditional Last Cell Lookup.

1] =IFERROR(IF(LOOKUP(9^9,INDEX(Matrix!$C$3:$N$28,MATCH(1,INDEX((Matrix!A$3:A$28=A2)*(Matrix!B$3:B$28=B2),0),0),0))>E2,LOOKUP(2,INDEX(1/((E2>=INDEX(Matrix!$C$3:$N$28,MATCH(1,INDEX((Matrix!A$3:A$28=A2)*(Matrix!B$3:B$28=B2),0),0),0))=TRUE),0),Matrix!$C$2:$N$2),"Not in list"),"Not in list")

This part is a 2 criteria (A2&B2) Conditional Lookup to return the matching range

...INDEX(Matrix!$C$3:$N$28,MATCH(1,INDEX((Matrix!A$3:A$28=A2)*(Matrix!B$3:B$28=B2),0),0),0)…

This part result >>

…...{"N/A","N/A","N/A","N/A",1490,1700,2146,2980,5000,6000,"N/A","N/A"}……

of which equal to range "Matrix!C3:N3" in matching A2&B2

Become >>

=IFERROR(IF(LOOKUP(9^9,Matrix!C3:N3)>E2,LOOKUP(2,INDEX(1/((E2>=Matrix!C3:N3)=TRUE),0),Matrix!$C$2:$N$2),"Not in list"),"Not in list")

2] =IFERROR(IF(LOOKUP(9^9,Matrix!C3:N3)>E2,LOOKUP(2,INDEX(1/((E2>=Matrix!C3:N3)=TRUE),0),Matrix!$C$2:$N$2),"Not in list"),"Not in list")

This part is a Last Cell Lookup >>

…...LOOKUP(9^9,Matrix!C3:N3)…….

…...LOOKUP(9^9,{"N/A","N/A","N/A","N/A",1490,1700,2146,2980,5000,6000,"N/A","N/A"})……

This part result >>

…...6000……

3] =IFERROR(IF(LOOKUP(9^9,Matrix!C3:N3)>E2,LOOKUP(2,INDEX(1/((E2>=Matrix!C3:N3)=TRUE),0),Matrix!$C$2:$N$2),"Not in list"),"Not in list")

This part is a Conditional Last Cell Lookup >>

…….LOOKUP(2,INDEX(1/((E2>=Matrix!C3:N3)=TRUE),0),Matrix!$C$2:$N$2)…….

…...LOOKUP(2,{#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,1,1,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!},{"Studio","Studio","1 Bed","1 Bed","2 Bed","2 Bed","3 Bed","3 Bed","4 Bed","4 Bed","5 Bed","5 Bed"})……

This part result >>

…..."3 Bed"….

4] The final evaluation of formula >>

=IFERROR(IF(6000>2475,"3 Bed","Not in list"),"Not in list")

final return >>

="3 Bed"

the desired result


Regards
Bosco
 
Last edited:
Thank you for the crystal clear explanation. However, could you please make me understand how below works and their significance.

1) Lookup(9^9,
2) LOOKUP(2

Thank you again for your valuable time.
 
Thank you for the crystal clear explanation. However, could you please make me understand how below works and their significance.
1) Lookup(9^9,
2) LOOKUP(2
Thank you again for your valuable time.

1) Last Cell Lookup :

LOOKUP(9^9,result range)

2) Conditional Last Cell Lookup :

LOOKUP(2,1/Condition1,result range)

or,

LOOKUP(9^9,1/Condition1,result range)


9^9 is simplified form of Excel BigNum 9.99999999999999E+307

upload_2017-4-30_15-52-20.png

Try to Google for "Excel BigNum", you will find a lot of explanation.

Regards
Bosco
 
Back
Top