Excel- LookUp Problem

[qlan]

Hi,

Below is what I have for my lookup:
Column A Column B
JobName Two Sides B&W 2HD top
Column D Column E
One Side B&W Folded 1
One Side B&W Pad in 100s 5
Two Sides B&W 3HD left
Two Sides B&W 3
Two Sides B&W 2HD top 500
One Side B&W 3HD left 6


LookUp Formulas
LOOKUP(B1,D16,E1:E6)

My result is 5 when it should equal to 500. Can someone please help me? Thanks so much

 

[SkipVought]

Hi,

Try this
[tt]
=SUMPRODUCT((B1=D16)*(E1:E6))
[/tt]


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[qlan]

Thanks, it works. However, would you please tell me what was wrong with my previous formula. I just want to understand a little bit more with lookup formula. Thanks so much

 

[firefytr]

Your original formula would need to be a Vlookup where you can specify an exact match or not ...

=VLOOKUP(B2,D1:E6,2,0)

or even an Index/Match combo ...

=INDEX(D1:E6,MATCH(B3,D16,0),2)

Now, depending on what you want should dictate which formula you use. SkipVought's formula will ADD all values in column E that grab a matching hit in column D from your specified value (B1 in this case). If you do not want to add all values, only return a single matching instance, use on of the two above I've supplied.

HTH

-----------
Regards,
Zack Barresse

 

[firefytr]

Oh, and btw, if you do not have a match, with the formulas I posted, you'll get the #N/A error. To suppress them, you can do something like this ...


=IF(ISNA(MATCH(B2,D15,0)),"",VLOOKUP(B2,D1:E6,2,0))

or ..

=IF(ISNA(MATCH(B3,D16,0)),"",INDEX(D1:E6,MATCH(B3,D16,0),2))

HTH

-----------
Regards,
Zack Barresse