Look up a value using index, match or sumproduct

[Faruk Hosen]

Hello,
I have tried to look up a value using index & match and using sumproduct.

=INDEX($C$2:$C$16,MATCH(F2,$B$2:$B$16,0),MATCH(H2,$D$2:$D$16,0))
=SUMPRODUCT(($D$2:$D$16=L6)*($B$2:$B$16=J6),$A$2:$D$16)

But not getting result.

File is attached herewith.

Someone please help.


thanks & regards
Faruk Hosen

 

[NARAYANK991]

Hi ,

See if this works. Check it out thoroughly.

Narayan

 

[Faruk Hosen]

Hi ,

See if this works. Check it out thoroughly.

Narayan

OK. Appreciate your work NARAYAN.
But it is very difficult for me when applying in 20K row.

Any other alternative please?

 

[Khalid NGO]

Any other alternative please?

Hi Faruk,

Or this with just enter:

=LOOKUP(2,1/($B$2:$B$16=F2)/($D$2:$D$16=H2),$C$2:$C$16)

Regards,

 

[Vishal Srivastava]

Another Approach:

=INDEX($C$2:$C$16,AGGREGATE(15,6,ROW($A$1:$A$16)/(($B$2:$B$16=F2)*($D$2:$D$16=H2)),1))


Thanks & Regards,
CMA Vishal Srivastava

 

[Faruk Hosen]

Hi Faruk,

Or this with just enter:

=LOOKUP(2,1/($B$2:$B$16=F2)/($D$2:$D$16=H2),$C$2:$C$16)

Regards,

Amazing!
Works!

Thank you

 

[Faruk Hosen]

Hi Faruk,

Or this with just enter:

=LOOKUP(2,1/($B$2:$B$16=F2)/($D$2:$D$16=H2),$C$2:$C$16)

Regards,

Just need to understand 2,1 better

is it of coloum no?

 

[Khalid NGO]

Hi Faruk,

No it is not a column number, it is a maximum lookup value.

If you evaluate the formula you will see how it works:

=LOOKUP(2,1/({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE})/($D$2:$D$16=H2),$C$2:$C$16)

1/TRUE = 1
1/FALSE = #DIV/0! (error value)

=LOOKUP(2,{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}/($D$2:$D$16=H2),$C$2:$C$16)

Each 1 will further divide by TRUE or FALSE:
=LOOKUP(2,{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}/{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},$C$2:$C$16)


1 for TRUE and #DIV/0! for FALSE:
=LOOKUP(2,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},$C$2:$C$16)

Since the array contains {1 and #DIV/0!} values only, the 2 is enough to lookup:
=LOOKUP(2,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},{"Bhognagar";"Mohammadpur";"Mohammadpur";"Mohammadpur";"Mohanpur";"Mohanpur";"Mohanpur";"Mohanpur";"Moricha";"Moricha";"Nijpara";"Nijpara";"Nijpara";"Nijpara";"Nijpara"})

Hope that helps.

Regards,

 

[Faruk Hosen]

Gr

Hi Faruk,

No it is not a column number, it is a maximum lookup value.

If you evaluate the formula you will see how it works:

=LOOKUP(2,1/({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE})/($D$2:$D$16=H2),$C$2:$C$16)

1/TRUE = 1
1/FALSE = #DIV/0! (error value)

=LOOKUP(2,{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}/($D$2:$D$16=H2),$C$2:$C$16)

Each 1 will further divide by TRUE or FALSE:
=LOOKUP(2,{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}/{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},$C$2:$C$16)

1 for TRUE and #DIV/0! for FALSE:
=LOOKUP(2,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},$C$2:$C$16)

Since the array contains {1 and #DIV/0!} values only, the 2 is enough to lookup:
=LOOKUP(2,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},{"Bhognagar";"Mohammadpur";"Mohammadpur";"Mohammadpur";"Mohanpur";"Mohanpur";"Mohanpur";"Mohanpur";"Moricha";"Moricha";"Nijpara";"Nijpara";"Nijpara";"Nijpara";"Nijpara"})

Hope that helps.

Regards,

Great!
Now clear to me!

You are a genius! A Ninja!