Which Do I Use? HLOOKUP, VLOOKUP, INDEX & MATCH, etc????!

[limeister]

Would you guys be able to help me out again?

I don't want VB code but a worksheet formula.

I have a table full of names and cars sold. The table tracks the person's progress. It is like a diary. It's a simplied version of the DB I am currently working with.

Is it possible to do the following things? I have basically 2 types of questions.

1)When was the last red car sold?
2)When was a car sold irrespective of color?

The answer to each question will be put in separate columns.
I have only included January but I will do this for the entire year of 2010 and also I will do this for all of the names in my database. I think there are about 300.


When was the last time ANDY sold a RED car?
The answer should be 1/12/2010
The answer will be put in ROW A COLUMN I

When was the last time ANDY sold a car irrespective of color?
The answer should be 1/12/2010
The answer will be put in ROW A COLUMN J

When was the last time BILL sold a RED car?
The answer should be 1/10/2010.
The answer will be put in ROW B COLUMN I

When was the last time BILL sold a car irrespective of color
The answer should be 1/11/2010
The answer will be put in ROW B COLUMN J


I have attached a thumbnail of a sample Database

 

[T. Valko]

Also can the formula be made so that it looks for two colors and returns the most recent date?

Like search for RED OR GREEN.

Try this array formula** :

=MIN(IF(INDEX(B2:H9,MATCH("Andy",A2:A9,0),0)={"Red";"Green"},B1:H1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Format as Date

 

[limeister]

Thanks for the reply guys. I will try these new formulas and keep you guys updated. I think this forum is great!!!

 

[limeister]

Can ANDY occur in more than one row?

Hello Aladin

Well, each name will be given a unique record so ANDY might occure more than once but each ANDY will be given a separate tracking number.

So I guess the real answer to your question is NO.


A bit of a round about way of answering. Sorry.

 

[limeister]

Try this array formula** :

=MIN(IF(INDEX(B2:H9,MATCH("Andy",A2:A9,0),0)={"Red";"Green"},B1:H1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Format as Date

Hello #NAME

My understanding of EXCEL increases everyday due to forums like this and replies from people like you.

Your formula didn't exactly work they way I wanted it too BUT no worries.
The formula would ONLY count if RED then GREEN was followed in that order in a particular row AND then it would return the date.

I was actually looking for a formula that would search if it was RED or GREEN regardless of how they are ordered and then return the most recent date.

I think the limitation is that the data needs to be ordered? I am not sure.

BUT here is what I did.

1) Used the "RED" formula
2) Created (copied) a "GREEN" formula.
3) Then just did a compare which was was more recent and return the date that was more recent.

It would be great if it would fit in one worksheet formula BUT it doesn't matter. All I had to do is create another column to do the comparison.

Cheers guys and thanks. I would say this question has been solved!
YEAH!!!

 

[Aladin Akyurek]

Hello Aladin

Well, each name will be given a unique record so ANDY might occure more than once but each ANDY will be given a separate tracking number.

So I guess the real answer to your question is NO.


A bit of a round about way of answering. Sorry.

Referring to your own exhibit:

Control+shift+enter, not just enter...

=MAX(
    IF(ISNUMBER(MATCH(INDEX($B$2:$H$9,MATCH("ANDY",$A$2:$A$9,0),0),{"RED","GREEN"},0)),
       $B$1:$H$1))

 

[T. Valko]

I haven't been around for a few days so this is a late reply.

Hello #NAME

Your formula didn't exactly work they way I wanted it too BUT no worries.
The formula would ONLY count if RED then GREEN was followed in that order in a particular row AND then it would return the date.

I was actually looking for a formula that would search if it was RED or GREEN regardless of how they are ordered and then return the most recent date.

Hmmm...

Works OK for me.