Vlookup query that checks 2 conditions

T

Twinson

I've posted this before without too much luck so here it goes again...

I'm trying to automate the creation of a vendor report that lists al
vendor sales. Basically I need a formula that, based on a unique vendo
number, will:

- check colomn A for the vendor number match

- then check column B to see if there is a buyer number (which mean
that the item is sold)

- then dumps then 'nth' occurance of the value / text from the column
specify (8)

I'm currrently using the formula:

{=IF(Catalog!B2:B428
0,INDEX(VendorReportData,SMALL(IF(VendorReportData=$N$13,ROW(Catalog!$A$2:$A$428)
ROW(Catalog!$A$2)+1,ROW(Catalog!$A$428)+1),2),2),0)}

It's the =IF(Catalog!$B$2:$B$428>0) part of the formula that isn'
doing what I want. I want it to check to see that the value in column
based on the nth row is >0. At the moment it is returning the nth valu
in the list but not checking to see that the above condition is me
also.

Should I be using the vlookup(s) formula for this
 
B

Bob Phillips

How about this formula

=INDEX(Catalog!I2:I428,MATCH(VendorReportData,IF(Catalog!B2:B428>0,Catalog!A
2:A428),0),1)

again it is an array formula

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Twinson

Thanks Bob, but the formula returns a #N/A result. It's almost like th
formula should be

{=INDEX(Catalog!I2:I428,MATCH(VendorReportData,IF(AND(Catalog!B2:B428>0,Catalog!A2:A428=N13),0),1))}

where the And checks for both conditions but this also returns the #n/
error.

Any other ideas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top