Ask Your Question
0

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

asked 2016-04-07 19:42:29 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2016-04-07 20:12:06 +0200

erAck gravatar image

updated 2016-04-07 20:15:00 +0200

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)))

edit flag offensive delete link more
1

answered 2016-04-07 20:57:49 +0200

karolus gravatar image

updated 2016-04-07 20:59:18 +0200

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

edit flag offensive delete link more

Comments

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.

n370 gravatar imagen370 ( 2016-04-07 23:20:01 +0200 )edit

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

https://en.wikipedia.org/wiki/Binary_...

karolus gravatar imagekarolus ( 2016-04-07 23:56:57 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-04-07 19:42:29 +0200

Seen: 108 times

Last updated: Apr 07 '16