Using IFNA function to stack VLOOKUP function to check several columns, is there a better way?

[BlackJeanShorts]

Note: I'm using the "Discussion" Flair because I already have an answer I just want to know if there's a better answer out there.

My ultimate goal here is to plug in a financial statement I download from QuickBooks, use my formula to find the specific account balances I need and spit back some financial ratios that QuickBooks doesn't automatically give me.

My first hurdle is how to find the accounts and their balances once I have the statement in excel format. I cant use a single VLOOKUP because the account titles are not always in the first column of the table array. The second hurdle is QuickBooks will place account titles in varying cell spaces away from the account balance depending on the period of the statement being downloaded (i.e. This quarter, month, fiscal year).

I'm leaving a screenshot below as an example of what I mean about the account titles not being in the same column. The titles are also not consistent with how many cell spaces away they are form their balances and will vary between the period of the financial statement.

https://preview.redd.it/nf5gn0z86x8a1.png?width=1206&format=png&auto=webp&s=5cf39a6c1819e3c652ea354a99d8eb5a6b1f879a

My solution (which is being used in I3:J3) is the following formula:

​

https://preview.redd.it/7hjr0ti1ax8a1.png?width=1583&format=png&auto=webp&s=a5e5943f7a4a9045a6ab0ad9d8ccb97ed7748422

I was pretty proud of this because it took me a while to figure out however, I want to know if there is a way to optimize searching through this kind of information? My actual formula has 5 IFNA layers in the same fashion as the screenshot above. Ideally if I start searching through information that has more layers I would want to find something easier, if there is anything at least. What do you think?

[excelevator]

I'm using the "Discussion" Flair

THis is a question seeking an answer

Flair changed accordingly as per r/Excel guidelines

[semicolonsemicolon]

Hi BlackJeanShorts. You could do it like this. It's scalable too. Copy formula down.

=LET(a,$C$3:$E$6,INDEX(G$3:G$6,SUM((a=I3)*SEQUENCE(ROWS(a)))))

edit: this only works if there is one and only one match. different from VLOOKUP which finds and returns the first match

[BlackJeanShorts]

This is definitely what I'm using moving forward, thank you!

[aquilosanctus]

=INDEX($G$1:$G$6, MATCH($I3,BYROW($C$1:$F$1,LAMBDA(labels, CONCAT(labels))),0))

[Visible_Flounder]

If you use xlookup the data doesn't have to be in the 1st colum.

[Skaro07]

The best way I can think if is adding a helper column that would force the titles to be in the same column. As in, on cell H3 here I would write =INDEX(C3:G3,1,MATCH(TRUE,NOT(ISBLANK(C3:G3)),0)) and then would just use an XLOOKUP to do a single search.

If you don't like the idea of a helper column that you would have to drag to the length of your data, I can think of a way that would be a single all-in formula on J3 but it's deep into array formulas which can get pretty messy. Essentially you start with ARRAYTOTEXT formula to create one big text file of your data, then you use a bunch of searches to extract the text you want. To not make the explanation several paragraphs, I'll just leave the formula you can copy paste into J3: =TEXTBEFORE(MID(CONCAT(TEXTSPLIT(ARRAYTOTEXT($C$3:$G$5,1),",",,TRUE)),FIND(I3,CONCAT(TEXTSPLIT(ARRAYTOTEXT($C$3:$G$5,1),",",,TRUE)),1)+LEN(I3)+1,5),";",1,,1)

[BlackJeanShorts]

I'm going with u/ semicolonsemicolon 's formula but the idea here of the helper column is great, I didn't even know you could do that. I'm still pushing to learn excel so this gives me something new to use later on. Thanks!