Trouble with IF and HLOOKUP Statement

[kskoulikaris]

Hello,

I have some trouble solving the following exercise: In column M on the worksheet ‘Purchase Invoices’ use an appropriate lookup function based on thedescription, together with an appropriate logical formula to show whether the supplier for eachinvoice is on the approved supplier list. The approved supplier list is shown by product on theworksheet named ‘Approved supplier by product’. If the supplier is on the list, then show ‘YES’. Ifnot, then show ‘NO’ in column M.

Below you'll see the data from the "Approved supplier by product" sheet, which I use for the HLOOKUP function:

[TABLE="width: 2499"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Pop up princess tent[/TD]
[TD]Pullalong telephone[/TD]
[TD]Waterbead set[/TD]
[TD]Size 4 football[/TD]
[TD]Soft football goals[/TD]
[TD]Poly-sword[/TD]
[TD]My first jewellery set[/TD]
[TD]Steven the steam engine and track[/TD]
[TD]Pushalong with blocks[/TD]
[TD]Night racer cars[/TD]
[TD]Meet the dinosaurs book[/TD]
[TD]Mega building blocks[/TD]
[TD]Mini tennis set[/TD]
[TD]Mini golf set[/TD]
[TD]Ladybird light cushion[/TD]
[/TR]
[TR]
[TD]Kids play[/TD]
[TD]Kids play[/TD]
[TD]Art for kids[/TD]
[TD]Mitre[/TD]
[TD]Outdoor play factory[/TD]
[TD]Poly toys[/TD]
[TD]Kids play[/TD]
[TD]Toymania[/TD]
[TD]Toymania[/TD]
[TD]Toymania[/TD]
[TD]Dino books[/TD]
[TD]Toymania[/TD]
[TD]Outdoor play factory[/TD]
[TD]Outdoor play factory[/TD]
[TD]Kids play

[/TD]
[/TR]
</tbody>[/TABLE]


And here's where I'm hitting a stumbling block:

[TABLE="width: 1712"]
<colgroup><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Description[/TD]
[TD]Internal Ref[/TD]
[TD]Financial Year[/TD]
[TD]Period[/TD]
[TD]Supplier[/TD]
[TD]Invoice ref[/TD]
[TD]Cost per unit[/TD]
[TD]RRP per unit[/TD]
[TD]Qty[/TD]
[TD]Net cost[/TD]
[TD]Total Gross[/TD]
[TD]Minimum age[/TD]
[TD]Approved supplier[/TD]
[TD]Mark up on cost[/TD]
[/TR]
[TR]
[TD]Ladybird light cushion[/TD]
[TD="align: right"]45901[/TD]
[TD]20X7[/TD]
[TD="align: right"]1[/TD]
[TD]Kids play[/TD]
[TD]KP78790[/TD]
[TD="align: right"]11.00[/TD]
[TD="align: right"]15.99[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]£44.00[/TD]
[TD="align: right"]£52.80[/TD]
[TD]48.00[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ladybird light cushion[/TD]
[TD="align: right"]45902[/TD]
[TD]20X7[/TD]
[TD="align: right"]2[/TD]
[TD]Kids play[/TD]
[TD]KP81239[/TD]
[TD="align: right"]11.25[/TD]
[TD="align: right"]15.99[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]£22.50[/TD]
[TD="align: right"]£27.00[/TD]
[TD]48.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ladybird light cushion[/TD]
[TD="align: right"]45903[/TD]
[TD]20X7[/TD]
[TD="align: right"]3[/TD]
[TD]Kids play[/TD]
[TD]KP90149[/TD]
[TD="align: right"]11.25[/TD]
[TD="align: right"]15.99[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]£22.50[/TD]
[TD="align: right"]£27.00[/TD]
[TD]48.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ladybird light cushion[/TD]
[TD="align: right"]45904[/TD]
[TD]20X7[/TD]
[TD="align: right"]4[/TD]
[TD]Kids play[/TD]
[TD]KP92901[/TD]
[TD="align: right"]11.00[/TD]
[TD="align: right"]15.99[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]£44.00[/TD]
[TD="align: right"]£52.80[/TD]
[TD]48.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Meet the dinosaurs book[/TD]
[TD="align: right"]45905[/TD]
[TD]20X7[/TD]
[TD="align: right"]1[/TD]
[TD]Dino books[/TD]
[TD]10394[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]3.99[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]£24.00[/TD]
[TD="align: right"]£28.80[/TD]
[TD]60.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Meet the dinosaurs book[/TD]
[TD="align: right"]45906[/TD]
[TD]20X7[/TD]
[TD="align: right"]3[/TD]
[TD]Dino books[/TD]
[TD]10490[/TD]
[TD="align: right"]3.05[/TD]
[TD="align: right"]3.99[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]£12.20[/TD]
[TD="align: right"]£14.64[/TD]
[TD]60.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Meet the dinosaurs book[/TD]
[TD="align: right"]45907[/TD]
[TD]20X7[/TD]
[TD="align: right"]4[/TD]
[TD]Dino books[/TD]
[TD]10699[/TD]
[TD="align: right"]3.05[/TD]
[TD="align: right"]3.99[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]£12.20[/TD]
[TD="align: right"]£14.64[/TD]
[TD]60.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Meet the dinosaurs book[/TD]
[TD="align: right"]45908[/TD]
[TD]20X7[/TD]
[TD="align: right"]6[/TD]
[TD]Dino books[/TD]
[TD]10840[/TD]
[TD="align: right"]3.05[/TD]
[TD="align: right"]3.99[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]£12.20[/TD]
[TD="align: right"]£14.64[/TD]
[TD]60.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mega building blocks[/TD]
[TD="align: right"]45909[/TD]
[TD]20X7[/TD]
[TD="align: right"]1[/TD]
[TD]Toymania[/TD]
[TD]313120414[/TD]
[TD="align: right"]9.50[/TD]
[TD="align: right"]15.99[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]£76.00[/TD]
[TD="align: right"]£91.20[/TD]
[TD]24.00[/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]


This is the formula I'm currently using in M7 (under the Approved Supplier header) that returns as N/A

=IF(HLOOKUP(E7,'Approved supplier by product'!A1:O2,2,TRUE),"Yes",IF(HLOOKUP(E7,'Approved supplier by product'!A1:O2,2,FALSE),"No"))


Any tips that can help me understand what I'm doing wrong would be greatly appreciated!

 

[Gerald Higgins]

Looks like homework, so it's probably not in your best interests if we do it all for you.

Here's one clue - I think you are misunderstanding the function of the TRUE and FALSE statements in your HLOOKUP formula.
Check out Excel help on what those statements actually do in that position in a lookup formula.