CODEWITHSUNDEEP
excel
Michi
Michi

Reputation: 5481

HLOOKUP/VLOOKUP to replace nested IF-functions

I have the following Excel spreadsheet:

        A             B             C                D             E
1                    Sale 1        Sale 2         Sale 3
2    Product A         0             0              400          Sale 3
3    Product B         0             0              300          Sale 3
4    Product C         0            200               0          Sale 2
5    Product D        800            0                0          Sale 1

In Columns B:D you can see the Sales for each product.

In Column E I want to show in which Sale the product was sold. Therefore, the Sale which is <> 0 should be shown.

I used the following formula so far:

=IF(B3<>0,B$2,WENN(C3<>0,C$2,WENN(D3<>0,D$2)))

This formula works in the upper case quite well but in my orignal example there are much more sales which would expand this formula massively. Is there a way to shorten the formula so it is not necessary to use so many nested IF-functions?

Maybe somethling like a HLOOKUP with a value <> 0 criteria?

Upvotes: 0

Views: 263

Answers (1)

Mrig
Mrig

Reputation: 11712

Following formula should be helpful:

=INDEX($B$1:$D$1,MATCH(TRUE,INDEX($B2:$D2<>0,),0))

Drag/Copy down as required.

See image for reference:

enter image description here

Upvotes: 1

Related Questions