Can someone tell me why this keeps returning a #N/A error when it shouldn't.
I'm using match to find the lookup value. On its own, the
"G"&MATCH(B11,$G$5:$G$780,0)+4 portion of the formula correctly evaluates to
G29, but when used in the vlookup, it gives me #N/A.

=VLOOKUP("G"&MATCH(B11,$G$5:$G$780,0)+4,$G$5:$I$78 0,2,FALSE)

=VLOOKUP(INDIRECT("G"&MATCH(B11,$G$5:$G$780,0)+4), $G$5:$I$780,2,FALSE)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Paige" wrote in message ...
| Can someone tell me why this keeps returning a #N/A error when it shouldn't.
| I'm using match to find the lookup value. On its own, the
| "G"&MATCH(B11,$G$5:$G$780,0)+4 portion of the formula correctly evaluates to
| G29, but when used in the vlookup, it gives me #N/A.
|
| =VLOOKUP("G"&MATCH(B11,$G$5:$G$780,0)+4,$G$5:$I$78 0,2,FALSE)

Does G29 mean you want to use the string "G29" or what's in the cell G29?

You could use:
=VLOOKUP(indirect("G"&MATCH(B11,$G$5:$G$780,0)+4), $G$5:$I$780,2,FALSE)

But easier:
=index($h$5:$h$780,match(b11,$g$5:$g$780,0)+4)




Paige wrote:

Can someone tell me why this keeps returning a #N/A error when it shouldn't.
I'm using match to find the lookup value. On its own, the
"G"&MATCH(B11,$G$5:$G$780,0)+4 portion of the formula correctly evaluates to
G29, but when used in the vlookup, it gives me #N/A.

=VLOOKUP("G"&MATCH(B11,$G$5:$G$780,0)+4,$G$5:$I$78 0,2,FALSE)

--

Dave Peterson

try this idea instead to index your match
=VLOOKUP(INDEX(B2:B8,MATCH(B11,B2:B8,0))+1,C2:C8,1 ,0)

--
Don Guillett
SalesAid Software

"Paige" wrote in message
...
Can someone tell me why this keeps returning a #N/A error when it
shouldn't.
I'm using match to find the lookup value. On its own, the
"G"&MATCH(B11,$G$5:$G$780,0)+4 portion of the formula correctly evaluates
to
G29, but when used in the vlookup, it gives me #N/A.

=VLOOKUP("G"&MATCH(B11,$G$5:$G$780,0)+4,$G$5:$I$78 0,2,FALSE)

Thanks to all of you; got it working with the 'indirect'. Will also try the
index method too; I need to become more familiar with both of these
obviously. Again, thanks again!

"Don Guillett" wrote:

try this idea instead to index your match
=VLOOKUP(INDEX(B2:B8,MATCH(B11,B2:B8,0))+1,C2:C8,1 ,0)

--
Don Guillett
SalesAid Software

"Paige" wrote in message
...
Can someone tell me why this keeps returning a #N/A error when it
shouldn't.
I'm using match to find the lookup value. On its own, the
"G"&MATCH(B11,$G$5:$G$780,0)+4 portion of the formula correctly evaluates
to
G29, but when used in the vlookup, it gives me #N/A.

=VLOOKUP("G"&MATCH(B11,$G$5:$G$780,0)+4,$G$5:$I$78 0,2,FALSE)