Hlookup help.

[Adnan Halai]

Hello Expert,

Greetings for the day.

Due to multiple lookup criteria, can someone please help me to bring Percentage in Attached file. I cannot add helper column or Row in Database tab as that will be auto-generated from other file.

Request if anyone can help me to figure out correct formula.

Many Thanks in Advance.

Regards
Adnan Halai

 

[bines53]

Hi ,

So that I will know better, you can specify the required values in C4:C7 .

Which version of Excel you have?

David

 

[Adnan Halai]

Thank you David.

I am using Excel 2010.

 

[bosco_yip]

Try......

1] Please select dropdown list in B1 and A3 as per required.

2] In B4, formula copy across and down :

=INDEX(INDEX(database!$B$2:$E$57,,MATCH($B$1,database!$B$1:$E$1,0)+MATCH(B$3,$B$3:$C$3,0)-1),MATCH($A$3,database!$A$2:$A$57,0)+($A$3<>"OVERALL")+MATCH($A4,$A$4:$A$7,0))

3] Please see attachment

Regards
Bosco

 

[Manny Singh]

Try......

1] Please select dropdown list in B1 and A3 as per required.

2] In B4, formula copy across and down :

=INDEX(INDEX(database!$B$2:$E$57,,MATCH($B$1,database!$B$1:$E$1,0)+MATCH(B$3,$B$3:$C$3,0)-1),MATCH($A$3,database!$A$2:$A$57,0)+($A$3<>"OVERALL")+MATCH($A4,$A$4:$A$7,0))

3] Please see attachment

Regards
Bosco

Hi Bosco,

Loving your work mate, I tried for 15 mins but couldn't get this formula, could you please explain what are you doing with so many matches, appreciate it.

Thanks, Manny

 

[Adnan Halai]

Thank you so much Bosco, Do I call you superstar or Saviour.

 

[bosco_yip]

Hi Bosco,

Loving your work mate, I tried for 15 mins but couldn't get this formula, could you please explain what are you doing with so many matches, appreciate it.

Thanks, Manny

Hi Manny,

=INDEX(INDEX(database!$B$2:$E$57,,MATCH($B$1,database!$B$1:$E$1,0)+MATCH(B$3,$B$3:$C$3,0)-1),MATCH($A$3,database!$A$2:$A$57,0)+($A$3<>"OVERALL")+MATCH($A4,$A$4:$A$7,0))

This red color highlighted part, to return the Lookup Range in respect of 2 criteria of "Date" and "Total"

and,

=INDEX(INDEX(database!$B$2:$E$57,,MATCH($B$1,database!$B$1:$E$1,0)+MATCH(B$3,$B$3:$C$3,0)-1),MATCH($A$3,database!$A$2:$A$57,0)+($A$3<>"OVERALL")+MATCH($A4,$A$4:$A$7,0))

This red color highlighted part, to return the Match Position no. in respect of 3 criteria in A3; check cell A3 not equal "OVERALL" and cell A4.

Try highlight the red area and pressing F9 to further investigate.
Regards
Bosco

 

[bines53]

Hi Bosco,

Perfect solution !

Allow once with a function INDEX,

=INDEX(database!$B$1:$E$57,MATCH($A$3,database!$A$1:$A$57,0)+($A$3<>"OVERALL")+MATCH($A4,$A$4:$A$7,0),MATCH($B$1,database!$B$1:$E$1,0)+MATCH(B$3,$B$3:$C$3,0)-1)

David