"Javier Diaz" wrote...
I want to start off by thanking you a million times for your help.
....

You're welcome.

You here that Microsoft, . . .
....

From these newsgroups, MSFT hears nothing. Warning: rant coming.

MSFT has no excuse for your original formula

=INDEX(Sheet1!$H$1:$N$1,,MATCH(TRUE,OFFSET(Sheet1! $H$1:$N$1,
MATCH(A1,Sheet1!$G$1:$G$6479,0)-1,)<"",0))

and my revision to it

=N(INDEX(Sheet1!$H$1:$N$1,MATCH(TRUE,INDEX(Sheet1! $H$1:$N$6479,
MATCH(A1,Sheet1!$G$1:$G$6479,0),0)<"",0)))

producing different results.

The first returns something like a range reference to a single cell, and the
second returns that cell's value. If that cell were K1, the simple reference
=K1 would have behaved the same as the result of the SECOND formula, i.e.,
it would have worked with your other formulas.

MSFT has NEVER provided a public explanation for the precise data type your
first formula returns. The 2 commas in sequence, producing a default 0 value
for the 2nd arg to INDEX makes INDEX return something that's not directly
usable. It's either not quite a range reference, or it's just a little more
than a range reference. Whichever, MSFT seems uninterested in documenting
what it is. So the rest of us (possibly excepting the MVPs, who may even be
subject to NDAs about @#$% like this) are left to speculate and to learn
that there are times one must wrap expressions in seemingly useless N, T or
CELL("Contents",..) calls.

Perhaps if the MVPs aren't subject to NDAs about this they could ask someone
on the Excel development team to explain the data type your original formula
returns, that is, if there's anyone on the Excel development team who knows.