2 way lookup

[mjimmy]

Hi,

I am trying to look for products from column B in column F. Once found I want to compare the 'capacity' with 'quantity'. If quantity is less than capacity for that product I want to display location, product code and quantity for that item.

Can this be done with INDEX and MATCH or will I have to use something else?

 

[ajetrumpet]

jimmy,

I don't use built-in functions much, but since no one else has given a solution here I would say you probably can't do this. At least in a simple manner. The reason being: you're asking to return an array of data (location, product code, quantity) for more than 1 record at a time. For instance, that would be true for product code "BRAS-PD-02". But if you're wanting to return just one piece of data for *each* record, one at a time and show the results, one result per cell, per row, then I would guess you could use INDEX and MATCH. Otherwise, i'm guessing you need code.