about INDEX function

  • Thread starter Thread starter deadfish
  • Start date Start date
D

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
 
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
 
You can't, using a single standard Index function. You will need to use a
more complex, conditional formula to do that:
Click to expand...
...

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))
 
Harlan Grove said:
Wrong! It can be done, but it requires creativity.
Click to expand...

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
 
Bernie Deitrick said:
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.
Click to expand...

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.
 
Back
Top