If I have a sheet with the following data, where the constants are always going to be the Load number and the Key...
| Load | Key | Size | Color | Type |
|---|---|---|---|---|
| 1 | Tom | XL | ||
| 1 | Jan | S | ||
| 2 | Tom | XL | Blue | |
| 2 | Jan | S | Green | |
| 3 | Tom | XL | Red | Pants |
| 3 | Jan | S | Green | Short |
...is it possible, to have Excel lookup the key, and only bring back the size, color and type for the max(load) or highest load number? So that it only shows the following?
| Key | Size | Color | Type |
|---|---|---|---|
| Tom | XL | Red | Pants |
| Jan | S | Green | Short |
CodePudding user response：
The short answer is Yes, that can be done. The following formula is one way to approach it:
{=INDEX($E$1:$E$7,MATCH(A10&B10&C10,$B$1:$B$7&$C$1:$C$7&$D$1:$D$7,0))}
Notes:
a) The above is how the formula would look in cell D10, assuming the tables are as shown in the image below.
b) This is an array formula and you don't enter the opening { or closing }. Rather, you 'commit' the formula using Ctrl, Shift and Enter.
CodePudding user response：
If I understood it right you want all the itens where load is 3 or max(Load), doesn't matter other parameters. My answer suppose you have Excel 365, you only need to place it once in cell H2 of the exemple.
=FILTER(Table[[Key]:[Type]];Table[Load]=MAX(Table[Load]);NA())
