Advance Excel Lookup function

I wanted to use lookup function to lookup for specific value and if the value doesn't exist, it needs to lookup other column.

If the amount is present in Amount1 column it needs to pick it up ..else it needs to pick the amount from amount2 column.

Output column should have all the values in single column

Try using BYROW() with an implicit intersection operator @

=BYROW(F2:F6,LAMBDA(x,@TOROW(FILTER(B2:D6,x=A2:A6,""),1)))

Or, try as mentioned by JvdV Sir, using MMULT()

• Formula used in cell H2

=MMULT(B2:D6*1,{1;1;1})

Use nested XLOOKUP(). Try-

=XLOOKUP(1,XLOOKUP(G2,$A$2:$A$6,$B$2:$D$6),XLOOKUP(G2,$A$2:$A$6,$B$2:$D$6),,1)

Well, perhaps another method to consider:

Use a helper column to get the max value for each row then index with match:

Then it could be the average of the values or the minimum etc etc

Also note that the order of the input does not need to match the order of the output.

Sum Up Rows of Filtered Data

In cell G2:

Basic (Copy Down)

=SUM(FILTER($B$2:$D$11,$A$2:$A$11=$F2,""))

Dynamic (Spills)

=LET(sl,A2:A11,sr,B2:D11,dl,F2:F6,
BYROW(dl,LAMBDA(r,SUM(FILTER(sr,sl=r,"")))))

Excel (Structured) Table

In the first cell (row) of the Amount column:

=SUM(FILTER(Table1[[Amt1]:[Amt3]],Table1[Fruit]=[@Fruit],""))

The remaining cells (rows) get populated automatically.