Changing part of cell reference in INDEX - MATCH formula
 

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

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

This one works:

=IFERROR(INDEX(Spilletider!$O$2:$R$2;MATCH($B2;Spi lletider!$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.

--
HebbeLille

Changing part of cell reference in INDEX - MATCH formula
 

=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(S pilletider!$A:$D;G1;0);0);G1);0)

--
Biff
Microsoft Excel MVP


"HebbeLille" wrote in message
...
Hopefully somebody can tell me what's wrong with this formula:

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

This one works:

=IFERROR(INDEX(Spilletider!$O$2:$R$2;MATCH($B2;Spi lletider!$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.

--
HebbeLille

Changing part of cell reference in INDEX - MATCH formula
 

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
---
"HebbeLille" wrote:
Hopefully somebody can tell me what's wrong with this formula:

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

This one works:

=IFERROR(INDEX(Spilletider!$O$2:$R$2;MATCH($B2;Spi lletider!$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.

--
HebbeLille

Changing part of cell reference in INDEX - MATCH formula
 

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.

--
HebbeLille


"Max" wrote:

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
---
"HebbeLille" wrote:
Hopefully somebody can tell me what's wrong with this formula:

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

This one works:

=IFERROR(INDEX(Spilletider!$O$2:$R$2;MATCH($B2;Spi lletider!$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.

--
HebbeLille

Changing part of cell reference in INDEX - MATCH formula
 

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?

--
HebbeLille


"Max" wrote:

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
---
"HebbeLille" wrote:
Hopefully somebody can tell me what's wrong with this formula:

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

This one works:

=IFERROR(INDEX(Spilletider!$O$2:$R$2;MATCH($B2;Spi lletider!$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.

--
HebbeLille

Changing part of cell reference in INDEX - MATCH formula
 

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
---
"HebbeLille" wrote:
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?

Changing part of cell reference in INDEX - MATCH formula
 

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..
--
HebbeLille


"Max" wrote:

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
---
"HebbeLille" wrote:
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?

Changing part of cell reference in INDEX - MATCH formula
 

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
---
"HebbeLille" wrote:
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..