How to find a row # in a table and how to check if a result of an INDEX is blank?

[helpneeded2]

I am looking to learn if there are better ways to optimize my formulas. Here is the current formula I am using to obtain data from a raw data table:

=IF(INDEX(table_rawData,$A2,XMATCH(I$1,table_rawData[#Headers],0))="","",INDEX(table_rawData,$A2,XMATCH(I$1,table_rawData[#Headers],0)))

Question #1:
Currently, I am using column A for manually recording row index numbers. Rather than do this though, I was wonder if there is a better way to find out what the row number in the table is, other than using the ROW() function, with a subtraction to offset the number of rows from the top of the page, such as ROW()-2 ?

Question #2:
Is there a way to avoid typing the INDEX formula twice?

 

[RasGhul]

maybe try

=ifna(INDEX(table_rawData,$A2,XMATCH(I$1,table_rawData[#Headers],0)),"")

 

[helpneeded2]

maybe try

=ifna(INDEX(table_rawData,$A2,XMATCH(I$1,table_rawData[#Headers],0)),"")

That will still output blank cells as "0", since a blank value is not a N/A value.

 

[jtakw]

Hi,

One way for your Question #2:

=IFERROR(1/(1/INDEX(table_rawData,$A2,XMATCH(I$1,table_rawData[#Headers],0))),"")

Note: Only works for numeric results.

 

[Fluff]

Another option as you have 365

=let(Return,INDEX(table_rawData,$A2,XMATCH(I$1,table_rawData[#Headers],0)) if(Return="","",Return))

 

[helpneeded2]

Another option as you have 365

=let(Return,INDEX(table_rawData,$A2,XMATCH(I$1,table_rawData[#Headers],0)) if(Return="","",Return))

Thank you.

For the rows, I think I have it working right now with using the new SEQUENCE function I am not too familiar with:

=IF(INDEX(table_rawData,SEQUENCE(ROWS(table_rawData[MasterPatronId])),XMATCH(C$1,table_rawData[#Headers],0))="","",INDEX(table_rawData,SEQUENCE(ROWS(table_rawData[MasterPatronId])),XMATCH(C$1,table_rawData[#Headers],0)))

I'll convert this now to the code you provided above. Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.