about INDEX function

[deadfish]

Hi,
Suppose I have a formula like this:

=INDEX(Sheet1!$A$1:B$11, MATCH(A4,Sheet1!$A$1:$A$11),2)

If besides A1 to B11 and A1 to A11, I need to refer 2 more cells: E1
and F11, how to write the formula so that I can search all the value
in A1 to B11 and E11, F11 also??

Thanks

 

[Bernie Deitrick]

You can't, using a single standard Index function. You will need to use a
more complex, conditional formula to do that:

=IF(blah, blah, IF(Other blah, Other blah, LastBlah))

HTH,
Bernie
MS Excel MVP

 

[Harlan Grove]

You can't, using a single standard Index function. You will need to use a
more complex, conditional formula to do that:

...

Wrong! It can be done, but it requires creativity.

=INDEX(LEFT(Sheet1!A1:B12,1024*(ROW(Sheet1!A1:B12)<=11))
&LEFT(Sheet1!E11:F11,1024*(ROW(Sheet1!A1:B12)>11)),A4&"",2)

or without hard-coding the change to the first subrange,

=INDEX(LEFT(OFFSET(Sheet1!A1:B11,,,ROWS(Sheet1!A1:B11)+1,),
1024*(ROW(OFFSET(Sheet1!A1:B11,,,ROWS(Sheet1!A1:B11)+1,))<=11))
&LEFT(Sheet1!E11:F11,
1024*(ROW(OFFSET(Sheet1!A1:B11,,,ROWS(Sheet1!A1:B11)+1,))>11)),x&"",2)

That said, if the extra subrange spanned only one row, it'd be easier to use

=INDEX((Sheet1!A1:B11,Sheet1!E11:F11),IF(A4>=Sheet1!E11,1,
MATCH(A4,Sheet1!A1:A11)),2,IF(A4>=Sheet1!E11,2,1))

 

[Bernie Deitrick]

Wrong! It can be done, but it requires creativity.

I did say "Standard" and if you think that the OP will be able to figure
out, adapt, or maintain any of your suggested formulas, you're far more
optimistic than I.

Bernie

 

[Harlan Grove]

I did say "Standard" and if you think that the OP will be able to
figure out, adapt, or maintain any of your suggested formulas, you're
far more optimistic than I.

We could argue the meaning of 'standard', but no point.

One point that should be made but hasn't been yet is that the OP would be
better off combining the E11:F11 into a single area range with A1:B11. If
E11:F11 must appear in a different place, then both A1:B11 and E11:F11
should be fed from a single area range that could be the source for the
formula as well as the visual layout. That introduces redundancy, but
maintenance would be easiest with such redundancy, making the formula simple
(it could become a simple VLOOKUP call) and making the visual layout simple
though formula-driven.