VLOOKUP FORMULA

  • Thread starter Thread starter MICHAEL
  • Start date Start date
M

MICHAEL

I AM TRYING TO CREATE A FORMULA THAT SEARCHES FOR THE
FIRST PRICE THAT IS EQUAL TO OR GREATER THAN AND RETURNS
THE DATE THAT IT APPEARS. BUT THERE IS A CRITERIA THAT IS
A REQUIREMENT TO MEET FOR THE RESULT TO BE RETURNED. IT
NEEDS TO BE EQUAL TO OR GREATER THAN A SPECIFIED DATE.

EG:
LOOKUP PRICE 33.00 ON OR AFTER 16/01/04

A B
1 PRICE DATE
2 33.00 15/01/04
3 29.00 16/01/04
4 33.50 17/01/04 THIS SHOULD BE THE MATCH. NOT B5
5 33.00 18/01/04
 
Not fully tested:

=INDEX(B1:B5,MATCH(1,(A1:A5>=33)*(B1:B5>=DATEVALUE("1/16/04")),0))

entered as an array formula with <Ctrl><Shift><Enter>.

Please don't type in ALL CAPS! It's equivalent to shouting.
 
Hey *Drno*,

You could try using a LOOKUP function. It's not very elegant, but it
gets the job done. Here is what I suggest:

Instead of starting your list in column A, start it in column B. You
will be using column A to mark your latest sale. In "Sheet2", go to
where you want the client's name to appear and type in

=LOOKUP(1,Sheet1!A1:B1000)

next go to where you want the product name to appear and type:

=LOOKUP(1,Sheet1!A1:C1000) <--- the "B" became a "C"

and so on; "D" for the date and "E" for the number.

now whenever you enter a new sale, enter "1" beside that sale, and
delete all other instances of "1". wherever the "1" lies in column A,
the info from that row will appear on sheet 2.

So there is an added benefit to this method. not only do you get to
keep all previous sales entries, but also, just in case you need to
reprint an old reciept, you just have to place a "1" in the appropriate
row.

I hope this helps!

a.
 
Back
Top