IF, THEN OR ISERROR VLOOKUP STATEMENT 1

[baudouxnorthrup]

My boss has an excel spreadsheet, and he has created a look up table for it. What he wants me to created a statement that will look at a6 and if it is "xxx" AND if b6 is "bbb" AND if c6 is "ccc", then he wants it to look at d6 and return that value.

The problem is I am not sure if I should be using and If then statement or a vlookup. I am working in a separate spreadsheet, but doing a lookup to another spreadsheet for the values. Is this possible? And if so, how?

 

[xlbo]

why not use both ;-)
=if(and(A6="xxx",B6="bbb",c6="ccc&quot,vlookup(d6,lookuprange,columns across,false),"&quot

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[baudouxnorthrup]

I guess I didn't explain really well.

I have this look up file that has data:

company container product price
ABC drum 6240 .005
DEF pack 6118 .007


In my spreadsheet that I want to create the formula it has:

company container product price
ABC drum 6240
DEF pack 6118


In the price field, I need to create a statement that will go to the first spreadsheet so that it looks up if company=abc and container=drum and product=6240, then return price.

 

[xlbo]

aaaaah - no can do I'm afraid - you need an extra column in your lookup field to the right of product but before price
this field would be the formula:
=A2&B2&C2
where company is in A, container in B and product in C
You would then use a lookup on THIS field (say in col D) like so
=vlookup(A6&B6&C6,lookupsheet!$D$2:$E$1000,2,false)

where your prices go on to row 1000 - adjust as necessary

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde