Lookup formula question

[PeterG]

Hi all,

I have a Lookup formula setup to validate what the user enters against
a list of stock codes, returning the item costs. Stock codes are
A001, A002 etc, B001, B002, etc, D001, D002 etc If I then enter a
stockcode starting with letter C, I get the item cost returned from the
last letter B stockcode. How can I get it to return P.O.A.

This is what I have at present.

=IF(C4="Fittings",(LOOKUP(F4,Fittings!$C$1:$C$5019,Fittings!$D$1:$D$5019)),0)*AL4

C4 - Parent stock item description
F4 - Stock code
AL4 - Quantity

Any help - greatly appreciated.

 

[Mangesh Yadav]

Try something like:

=IF(ISNA(MATCH(F4,Fittings!$C$1:$C$5019,0)),"POA",IF(C4="Fittings",(VLOOKUP(
F4,Fittings!$C$1:$D$5019,2,FALSE),0)*AL4)


Mangesh

 

[PeterG]

That looks good, how would i set it up if I have 6 stock code listing
on 6 seperate worksheets.

When I try this formula ....

=IF((OR(ISNA(MATCH(F4,Duct!$C$1:$C$5000,0)),ISNA(MATCH(F4,Extra!$C$1:$C$5000,0)),ISNA(MATCH(F4,Fittings!$C$1:$C$5000,0)),ISNA(MATCH(F4,Flex!$C$1:$C$5000,0)),ISNA(MATCH(F4,Other!$C$1:$C$5000,0)),ISNA(MATCH(F4,Silencer!$C$1:$C$5000,0)))),"POA",
")

It always returns the POA answer, even if one of the ISNA(Matc
statements returns a false state

 

[PeterG]

I use an AND instead of an OR .... thats fixed it.