CODEWITHSUNDEEP
excelexcel-formula
Jay
Jay

Reputation: 79

Find last entry of a column if another column contains a certain text

I'm trying to retrieve the last entry value (number) from a column only if a certain text exists in another column within the same row.

I can retrieve the last entry no problem. But I'd like to first match "BabyFoods" in column A before getting the value. Something like the SUMIF function that checks for certain values in a column before adding things in another column.

=LOOKUP(2, 1/(ISNUMBER(Transactions!H9:H1006)), Transactions!H9:H1006)

Upvotes: 0

Views: 597

Answers (2)

Domenic
Domenic

Reputation: 8104

To find the last cell in Column A that contains BabyFoods, and return the corresponding value from Column H, try...

=LOOKUP(2,1/(Transactions!A9:A1006="BabyFoods"),Transactions!H9:H1006)

To find the last cell in Column A, where the corresponding cell in Column H contains a number, try...

=LOOKUP(2,1/((Transactions!A9:A1006="BabyFoods")*(ISNUMBER(Transactions!H9:H1006))),Transactions!H9:H1006)

Upvotes: 1

JvdV
JvdV

Reputation: 75840

Small example, hopefully it's usefull for your situation:

enter image description here

Formula in D1:

=MAX(INDEX((A2:A10="BabyFoods")*(B2:B10="X")*ROW(A2:A10),))

This will return the last row with the combination of criteria "BabyFoods" in column A and "X" in column B. You could apply another INDEX formula, if you would want to retrieve the value of a column.

Upvotes: 1

Related Questions