vlookup query

[ajduk1]

Hi
I have a query that returns the following:
Prod w/h cost
N1141D 90 0
N1141E 90 0
N1146A 01 0.95
N1146A 90 0.95
N1146B 90 0
N1146C 01 0.6
N1146C 90 0
N1146D 90 0
N1146D 01 0
N1146E 90 0
N1146E 01 0
N1147A 01 0.13
N1147A 90 0
N1147B 90 0
N1147C 01 0.12
N1147C 90 0
N1147D 90 0
N1147D 01 0
I have a vlookup using the table above as the table it is pulling data from. NB the table has a name defined as "product"

As you can see a number of products are in 2 warehouses i.e. 90 & 01.
If the product has the same cost in both that is fine, or if the product is only in 1 warehouse (fine), however if it is in 2 w/h and they have differing costs (in the case of N1147C)
I need to pull the 01 warehouse cost only. also if the cost = zero I need to change to 0.01 I have done this in the lookup(below) but I still need to do the warehouse bit.

=IF(VLOOKUP($A13,PRODUCT,78,FALSE)=0,0.01,VLOOKUP($A13,PRODUCT,78,FALSE))


Thanks for any help in advance.
AJD


AJD

 

[AnotherHiggins]

You can do that simply by sorting your PRODUCT table. Data > Sort, first sort is by Prod, Ascending, second sort is by w/h, Ascending. When using the last argument FALSE (which you are), Vlookup will always return the first exact match.

BTW, I'd suggest a different approach to change your zeros to 0.01s:
[tab][COLOR=blue white]=Max(VLOOKUP($E2,PRODUCT,3,FALSE), 0.01)[/color]



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.