Need Help With Formula

[it's an excel world]

Hello all,

I need a formula that will accomplish the following...

I want to look in cell AQ2 and if it is not blank, I need to look at the values in cells A4 & AN4. Then look down both columns A & AN until I locate the last row in which the values in columns A & AN mirror those of cells A4 & AN4, and return the value in column D of that particular row.

I hope this information is sufficient.

Thank you in advance!

 

[T. Valko]

Hello all,

I need a formula that will accomplish the following...

I want to look in cell AQ2 and if it is not blank, I need to look at the values in cells A4 & AN4. Then look down both columns A & AN until I locate the last row in which the values in columns A & AN mirror those of cells A4 & AN4, and return the value in column D of that particular row.

I hope this information is sufficient.

Thank you in advance!

Does that mean these values may appear together on more than one row?

 

[it's an excel world]

Hi T. Valko,

Yes, the values in column A & AN will appear together in more than one row. However, the file is sorted by column AN so you will not have very far to go to locate the last row where the values in column A & AN match.

Thanks!

 

[T. Valko]

Hi T. Valko,

Yes, the values in column A & AN will appear together in more than one row. However, the file is sorted by column AN so you will not have very far to go to locate the last row where the values in column A & AN match.

Thanks!

OK, what version of Excel are you using?

 

[it's an excel world]

2010

 

[it's an excel world]

Hi T. Valko,

I wanted to clarify my last statement. When I said you will not have far to go to located the last row in which the values in columns A & AN match, I mean these values match from the first row, not that the values in columns A & AN match each other, they will never match.

Column A has a date and column AN has a unique part number. I just want to locate the last row in which this date and part number are identical to those in row 4 (original email) and return the value in column D of that last row.

Thanks again!

 

[T. Valko]

Hi T. Valko,

I wanted to clarify my last statement. When I said you will not have far to go to located the last row in which the values in columns A & AN match, I mean these values match from the first row, not that the values in columns A & AN match each other, they will never match.

Column A has a date and column AN has a unique part number. I just want to locate the last row in which this date and part number are identical to those in row 4 (original email) and return the value in column D of that last row.

Thanks again!

Try this...

Criteria in A4 and AN4.

Data in the ranges A6:A15, D6:D15, AN6:AN15.

=IF(AQ2="","",INDEX(D:D,LOOKUP(2,1/((A6:A15=A4)*(AN6:AN15=AN4)),ROW(A6:A15))))

 

[it's an excel world]

T. Valko,

You are brilliant and amazing! This works to perfection!


Thank you very much,

 

[T. Valko]

T. Valko,

You are brilliant and amazing! This works to perfection!


Thank you very much,

You're welcome. Thanks for the feedback!

 

[it's an excel world]

Hi T. Valko,

I was hoping you could explain to me or point me in a direction to better understand exactly what your formula is telling excel to do. I think I understand the index and lookup portion, but am curious about the the 2,1 prior to the lookup and the last part with ROW(A6:A15))))

Thanks for all you help!