Search code examples
excelexcel-formulaspreadsheetexcel-2013vba

Excel - Selecting cell based on other column values

I am trying to select the correct cell based on the values of adjacent columns. For example -

Sheet 2 - This acts as a database of sorts holding all data/values
Sheet 2

Sheet 1 - The "Result" should populate with the "Item" from Sheet 2. The filter criteria is "Value" needs to be less than or equal to (<=) "Rating" and for multiple matches select the lowest "Sell" item. So, Item 3 has a value of 650 which means the applicable Items from Sheet 2 are G,H,J,K,L,M however the "Result" is populated with "G" because that has the cheapest "Sell" value.
Sheet 1

How can I write this for all "Result" cells? I am not well versed in Excel but I do know how to query a database such as MySQL, if that helps.

Thank you!

Solution

  • Use this formula in C2 and copy down:

    =INDEX(Sheet2!A:A,MATCH(AGGREGATE(15,6,Sheet2!$C$2:$C$14/(Sheet2!$D$2:$D$14>=B2),1),Sheet2!C:C,0))

    If you get Office 365 Excel then this would be better:

    =INDEX(Sheet2!A:A,MATCH(MINIFS(Sheet2!$C:$C,Sheet2!$D:$D,">=" &B2),Sheet2!C:C,0))