Excel Formula

RacingDriver

Is it possible in Excel to return a value from the last row of a table which meets a certain criteria based on another column?

For example in the data below if my criteria is to look for Item A, I want to return the item price from the last row, so should get £4.

Item Name          Item Price
Item A                  £1
Item A                  £2
Item B                  £3
Item A                  £4
Item C                  £5
Item B                  £6

GrubbyGirl_2

Just sort Item Name Column and the prices will also sort 

uknick

I assume he wants to automate the selection.  I guess you're going to have to use Visual Basic and arrays or tables tables.  I'll admit, something I've managed to avoid over the years.

Themoneyshot

You could use a pivot table to easily return the max value for each or any of column A. 

Andy_L

Vlookup would return the first one that matches, rather than the last. Can you re-sort the columns to reverse them?

HillStreetBlues

I'm only a very basic Excel user but would this work for you? =XLOOKUP(A3,(A5:A11),(B5:B11),,,-1)
So my examples  would find "Item A" £4  "Item B" £6

Heedtheadvice

A pivot table is probably the most useful way of doing data analysis.

In Excel there are many ways to achieve the result and choosing one depends upon how you want to do something.

 The first response is easy using built in filtering and sorting but it does not use the cell you have the criteria stored in.

With a pivot you can slso do manual filtering and use the max function to return the result.

Rather than write how to automate that pivot table from your criteria cell, can I point you to the method shown on the website superuser.com answer

YES it's very much possible that you can use a Cell Reference to Filter records while using the Pivot Table.

let me discuss one simple example. Suppose you have database in Range A2 to D20. Contains Headers for example Date, City, Name & Sales. Now to work with Cell reference to filter, steps shown below should be followed.

1. Create a New Column E.
2. write a formula like =and(D2<=1000,D2>=5000), press Enter.      [ in your case @RacingDriver it will be a simple ( assuming ref cell is F1 and  Items A,B, C etc is in col A starting row 2 ) = A2 = $F$1 ]
   
3. Drag down the formula till end,(You find True & False as per Data).
4. While building the Pivot table, move New Field(Column) to Report Filter OR to Slicer (if you have Excel 2010 or higher version).
5. Choose True/False from the Filter.
6. Go to Pivot-table Tools Options & hit Refresh.

You find Excel has included the New Field's effect and as per it shows the result.

NB: Remember in Place of TRUE you can use FALSE option also, you can use any possible method (Formula is better way) which can help Excel to Filter record.

@Racingdriver note that one of the most important aspects in Excel is to ensure unwanted actions are not produced. For example the Price column needs to be numeric ( formatted as currency ) thus not I clouding the pound sign in the cell - being shown by the currency format.

Further the pivot table needs to have the option set to update upon workbook opening or selecting update pivot when the items/price data is changed.

DullGreyGuy

Andy_L said:

Vlookup would return the first one that matches, rather than the last. Can you re-sort the columns to reverse them?

There is no need to reverse it, you can use the newer XLookUp and specify Search Mode to be last to first (-1) rather than the default first to last (which was the only option on the older VLookUp)

outtatune

What are you actually trying to do? What do each of the rows with 'Item A' represent?

Miser1964

1. You'll need to hold the data in an Excel Table. Identify your table and columns: Let's say you have a table named "Table1" with columns "Name" and "Price", and you want to return a value from "Price" where "Name" meets a certain criteria.

2. Use the MAX function to find the last row that meets your criteria: Assuming your criteria is based on "Name", you can use the MAX function along with the IF function to find the row number of the last occurrence where your criteria is met. For example, if your criteria is "Item A", you can use the following formula:

   =MAX(IF(Table1[Name]="Item A", ROW(Table1[Name]), 0))
   

This will return the row number of the last occurrence where "Name" equals "Item A".

Use INDEX and MATCH functions to return the value from "Price": Once you have the row number, you can use the INDEX and MATCH functions to retrieve the value from "Price". Assuming your table starts from row 2 (header row being row 1), you can use the following formula:

=INDEX(Table1[Price], MATCH(MAX(IF(Table1[Name]="Item A", ROW(Table1[Name]), 0)), ROW(Table1[Name]), 0))

This formula will return the value from "Price" where the last occurrence of "Item A" in "Name" is found.

Remember to adjust the criteria in the formulas according to your specific needs; you could put the criteria into a cell rather than a value in the formulae. 

double_dutchy

XLOOKUP looks like a good option, but if you are using an older version of Excel this LOOKUP formula will work for you:

=LOOKUP(2,1/(A:A="Item A"),B:B)