CODEWITHSUNDEEP
excelexcel-formula
Edvard Haugland
Edvard Haugland

Reputation: 125

Return value larger than X with INDEX(MATCH)

Please some help with the following:

In Excel, I have this table:

Processors by GHz

And I have this formula to lookup the combination of "Cores per host" and "GHz per host" that it satisfies the values of "Required cores per host" and "Required GHz per host"

=(INDEX(CPUData;MATCH(1;(CoresHost>C66)*(ghzHost>C36/C8);0);1))

Where CPUData is the table without headers, CoresHost and ghzHost are the defined names of the columns containing the values in the table above.

The formula is working, but the problem is that instead of returning the value larger than what is required, it's returning the value that is smaller.

For example, if the processor that matches the requirement or exactly exceeds it is the E52630, it will return the E52643 instead.

I have tried using -1 and 0, but any combination of those I've tried it gives me either an error or a completely wrong value.

How can I get it to return the processor that it exactly meets or exceeds the requirement?

Upvotes: 0

Views: 246

Answers (1)

EEM
EEM

Reputation: 6659

Assuming you are using an Excel Table (i.e. ListObject). If so, instead of using Defined Names, use the "BuiltIn Names" of the ListObject.

Try this formula instead:

=(INDEX(CPUData,MATCH(1,
(CPUData[Cores per Host]>C66)*(CPUData[GHz per host]>(C36/C8)),0),1))

Or this variation which limits the index to the Processor column instead of referencing the entire Table:

=(INDEX(CPUData[Processor],
MATCH(1,(CPUData[Cores per Host]>C66)*(CPUData[GHz per host]>(C36/C8)),0),1))

It returns E52630E (2,2GHz, 10Cores) with the following values: C66 = 144; C36 = 144 & C8 = 5

This works regardless of the blank record at beginning of the table.

Upvotes: 1

Related Questions