Changing part of cell reference in INDEX - MATCH formula

H

HebbeLille

Hopefully somebody can tell me what's wrong with this formula:

=IFERROR(INDEX("Spilletider!"&O&G$1:U&G$1;MATCH($B2;"Spilletider!"&A&G$1:D&G$1;0));0)

This one works:

=IFERROR(INDEX(Spilletider!$O$2:$R$2;MATCH($B2;Spilletider!$A$2:$D$2;0));0)

but I want to replace the number 2 in
$O$2, $R$2, $A$2 and $D$2 with the value from row 1.
(In the example the value 2 is in cell G1 in the same tab as the formula is
in,
and the column shall change when the formula is copied one cell to the
right, the value G should be H.)

The value $B2 shall not be changed, the value here changes for each row.
 
T

T. Valko

=IFERROR(INDEX("Spilletider!"&O&G$1:U&G$1;MATCH($B2;"Spilletider!"&A&G$1:D&G$1;0));0)

Without having to use INDIRECT, see if this does what you want:

=IFERROR(INDEX(Spilletider!$O:$U;MATCH($B2;INDEX(Spilletider!$A:$D;G1;0);0);G1);0)
 
M

Max

For it to work, you'd need to use INDIRECT to resolve the concatenated
strings (with the embedded variables) that you're trying to compose in your
expression, something like this, untested
=IFERROR(INDEX(indirect("'Spilletider'!"&"O"&G$1&":U"&G$1);MATCH($B2;indirect("'Spilletider'!"&"A"&G$1"&":D"&G$1);0));0)

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
H

HebbeLille

Sorry, I get an error in the last two G$1's. (The last INDIRECT).

Seems to me the MATCH doesn't like INDIRECT as lookup array.

INDIRECT("'Spilletider'!"&"A"&G$1"&":D"&G$1), Excel points out G$1 to be an
error.
 
H

HebbeLille

Tested out this one, and it works:

=IFERROR(INDEX(INDIRECT("'Spilletider'!"&"O"&G$1&":U"&G$1);MATCH($B2;INDIRECT("'Spilletider'!A2:D"&G$1);0));0)

But, as you can see, I had to remove the !"&"A"&G$1"&":D" with !A2:D".

Any idea how I can get this part also to work as intended?
 
M

Max

Think this should work, as-is (albeit untested*)
=IFERROR(INDEX(INDIRECT("'Spilletider'!O"&G$1&":R"&G$1);MATCH($B2;INDIRECT("'Spilletider'!A"&G$1&":D"&G$1);0));0)
where the variable is G1: 2

*as my xl03 doesn't have IFERROR, and my separators are commas, not
semi-colons. But I believe the expression above should work for you

voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
H

HebbeLille

Hi, and thanks for the effort.

You are quite right that it works for this particular cell, but when the
formula is like this, it doesn't automaticly change when copying in the
spreadsheet.
The formula now is locked to cell G1.

The purpose of the formula is to be locked to row 1, and automaticly change
the column G to H, I, J, K and so on.

Thanks for helping, appreciate if there is a solution of the last tricky bit..
 
M

Max

Think I lost you there. The variable cell is defined in the formula as: G$1,
which means when you copy it across it'll change to H$1, I$1, etc which is as
per your intents as posted earlier?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find the next row... 0
Adding another if statement to an index formula 0
VLOOKUP & Data Validation 0
Index and Match 8
Index Match function for lookup HELP 5
match formula 2
Index and Match 3
INDEX MATCH SMALL 13

Top