Can't use function CELL with CONCATENATE to retrieve cell data from another sheet

[n370]

Hi,

I have a cell with the following code:

=CELL(‘contents’, CONCATENATE("$‘Sheet 2’.$A$", MATCH(B98, $‘Sheet 2’.$B:$B)))

Can anybody please help me figure out why it’s yielding a #NAME? error?

Thank you!

[erAck]

The CELL function expects a reference as second parameter instead of a string. To convert a string to a reference use the INDIRECT function. However, you can completely omit the call to CELL, it’s superfluous, and just use the result of INDIRECT, so INDIRECT(CONCATENATE("$'Sheet 2'.$A$", MATCH(B98, $'Sheet 2'.$B:$B)))

[karolus]

Hallo

… or better:

=INDEX($'Sheet 2'.$A:$A, MATCH(B98, $'Sheet 2'.$B:$B))

If Column B in Sheet 2 is not sorted use:

=INDEX($'Sheet 2'.$A:$A, MATCH(B98, $'Sheet 2'.$B:$B, 0))

Karolus

[n370]

Thanks for clarifying the usage of MATCH. In my case the default value didn’t worked, I found I had to pass 0 to the third parameter without really understanding why.

[karolus]

It’s because of different search-algrithms, slow linear search versus fast binary search
obviously the binary search needs sorted search arrays

Binary search algorithm