Complex formula but not sure which one to use.

[tarsenul]

I am trying to compare one cell value to 7 other cell values and populate the one that is the next highest.

For example: I want to compare B2 to all of the rest of the columns and determine which one is the next highest and return that heading.
So, the outcome on Item 1 would be "ASP" because that is the next value up.
The outcome on Item 2 would be "10% discount" because column B is higher than F but lower than E.
I hope this makes sense. I appreciate any input.

 

[ExcelToDAX]

How about

=INDEX(SORT(TRANSPOSE(FILTER(C2:I2,C2:I2>B2))),1)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 2021 or 365 try

=XLOOKUP(B2,C2:J2,$C$1:$J$1,,1)

 

[tarsenul]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 2021 or 365 try

=XLOOKUP(B2,C2:J2,$C$1:$J$1,,1)

This is it!!! Thank you and thanks for the tip on account details. I haven't used this site in a long time.

How about

=INDEX(SORT(TRANSPOSE(FILTER(C2:I2,C2:I2>B2))),1)

ExcelToDax - this worked but didn't pull in the name.
Fluff - this worked exactly as I was looking for.
Thank you both for your quick response and Fluff I'll be sure to check out the Account Details that you mentioned.

 

[Fluff]

Glad we could help & thanks for the feedback.