Trying to convert long formulas to use XLOOKUP

Question

Level 1

13 points

Trying to convert long formulas to use XLOOKUP

I have 2 long formulas that make use of VLOOKUP and I'm trying to convert them to using XLOOKUP because I would like to move certain columns around. (It's been a long time since I created these formulas and it has taken me a while to figure it out, and now I can't quite manage to figure it out how to concert it to XLOOKUP... Any help would be appreciated.)

The following formula is in column K (all rows but the header)

=IF(D3≠"",IFERROR(IF(D3="TRUE",VLOOKUP(MAXIFS(A,A,"<"&A3,E,E3),A:K,11,FALSE)+(F3×G3+H3),VLOOKUP(MAXIFS(A,A,"<"&A3,E,E3),A:L,12,FALSE)×J3),G3×F3+H3),"")

And the following formula is in column N (all rows but the header)

=IFERROR(IF(D3="TRUE",VLOOKUP(MAXIFS(A,A,"<"&A3,E,E3),A:K,9,FALSE)+(F3×G3+H3),VLOOKUP(MAXIFS(A,A,"<"&A3,E,E3),A:L,12,FALSE)×M3),G3×F3+H3)

iMac 27″

Posted on Apr 22, 2024 2:20 PM

Reply

Answer 1

Urbiter Author

Level 1

13 points

Apr 22, 2024 3:51 PM in response to Urbiter

This is what I came up with after removing some extra stuff but it does not work.

Original formula:

=IF(D4="BUY",VLOOKUP(MAXIFS(A,A,"<"&A4,E,E4),A:K,11,FALSE)+(F4*G4+H4),VLOOKUP(MAXIFS(A,A,"<"&A4,E,E4),A:L,12,FALSE)*J4)

New formula:

=IF(D4="TRUE", XLOOKUP(MAXIFS(A, A, "<"&A4, E, E4), A, K, 0,0,) + (F4×G4+H4), XLOOKUP(MAXIFS(A, A, "<"&A4, E, E4), A, L, 0,0,) × J4) 

I get the formula can't reference it's own cell error.

Reply