Index Match?

[erutherford]

On worksheet "input" in C31, I would like to insert the results of a search on worksheet "hstry" by first searching in "Col F" for the words "oil Change" then search the same worksheet col D for the highest number (mileage).

thanks as always

 

[Nishant94]

Maybe something like this in C31 in input sheet?

=MAX(IF(hstry!F:F="Oil Change",hstry!D:D))

As it is an array formula after putting the formula hit Ctrl+Shift+Enter and not just Enter.

 

[erutherford]

The formula returns a zero. I have four values in "D:D" the highest is 6001, so the formula should return that value.

 

[erutherford]

I found this and it seems to work fine

{=MAX(IF(Hstry!F2:F11="Valve Adjustment",Hstry!D2:D11))}

.

You have to put "{}" brackets around it, can't remember how that done, should be hard to find.

 

[Nishant94]

I found this and it seems to work fine

{=MAX(IF(Hstry!F2:F11="Valve Adjustment",Hstry!D2:D11))}

.

You have to put "{}" brackets around it, can't remember how that done, should be hard to find.

You must have pressed only Enter after putting the formula instead of Ctrl+shift+Enter, that's the reason you got 0 intstead of 6001.

I mentioned in my post to press Ctrl+shft+Enter instead of just Enter.

Those curly braces are automatically added when you press Ctrl+Shift+Enter after putting the formula in any cell. You cannot just manually put those curly braces.

 

[Nishant94]

You have to put "{}" brackets around it, can't remember how that done, should be hard to find.

To know more about array forumlas you can refer to this video:

https://www.youtube.com/watch?v=vqfikSMx7mE

 

[erutherford]

yep you did and it also works fine!
Thanks again.