Vlookup of an if statement return

[James]

Hey all,

Ok here is what I am trying to do.

I have a sheet that in the first column I have an "if" statement that will
either level the blank empty or return the value "Overdue"

On a second sheet I want to be able to use VLookup or a similar function to
be able to check column "a" on my first sheet for all references to Overdue
and return the corrosponding value in column "b".

The problem I am having is that I can find the first "Overdue" and get my
column "b" text but I want to set it up to go on and find the next one and
then the next.

If this is not clear enough let me know and I will try to provide more
information.

 

[Biff]

Hi!

Is each instance of "overdue" associated with a different
value? For example, an account number or a name?

Here's a basic formula that will do what you want.

Assume sheet1 A1:A20 contains either "overdue" or is blank.
B1:B20 is the data you want to extract if the
corresponding cell in column A contains "overdue".

In sheet2 A1 enter this formula with the key combo of
CTRL,SHIFT,ENTER:

=INDEX(Sheet1!B$1:B$20,SMALL(IF(Sheet1!
A$1:A$20="overdue",ROW($1:$20)),ROW(1:1)))

Copy down until you get #NUM! errors meaning the data has
been exhausted.

If "overdue" is associated with a specific value (account
number, name, date) then you'll have to post back with
that info for a more specific solution.

Biff

 

[CLR]

Instead of just using "Overdue" in your IF formulas, I recommend you include
some sort of Account Number code, like "Overdue12345", and then do the
VLOOKUP on that code...........

Vaya con Dios,
Chuck, CABGx3