CODEWITHSUNDEEP
excelexcel-formulavba
user5656611
user5656611

Reputation:

Trying to concatenate a function and a value in a cell

I have this:

=MATCH('Plot Data - RAF'!B171,'MA Base'!J:J,0)

That gives me the row on a sheet where a certain string matches. The result is 88. I want to use that 88 in this function.

=INDEX('MA Base'!$K$88:$AN$88,1,MATCH(AS4,'MA Base'!$K$10:$AN$10,0))

Something like this:

=INDEX('MA Base'!$K$"& 88 &":$AN$"& 88 &",1,MATCH(AS4,'MA Base'!$K$10:$AN$10,0))

I can do it in VBA but I'm not sure if it's possible just by concatenating a string into a function. Basically, I don't want to use VBA for this.

Upvotes: 2

Views: 56

Answers (1)

Stian
Stian

Reputation: 187

You can use INDIRECT() as suggested, but I would build a nested index and use the ability of index() to find both column and row.

The very thing you suggest:

=INDEX('MA Base'!$K$"& 88 &":$AN$"& 88 &",1,MATCH(AS4,'MA Base'!$K$10:$AN$10,0))

is better written like this:

=INDEX('MA Base'!$K:$AN,88,MATCH(AS4,'MA Base'!$K$10:$AN$10,0))

Now it is just a matter of creating that "88" in the same formula. Thus:

=INDEX('MA Base'!$K:$AN,MATCH('Plot Data - RAF'!B171,'MA Base'!J:J,0),MATCH(AS4,'MA Base'!$K$10:$AN$10,0))

In this index is first your (k:an) array, then a index for the row number you are looking for (that returns 88), then your column reference at row 10.

Upvotes: 1

Related Questions