Lookup to the Left

[tuph]

I am using the formula below (adapted from one found on this forum - thanks!) and it works as far as it goes. However, I need to have it in all cells in column E (it's a data series for a chart), but those fields should be left empty if no match is found in B2:B7. Any suggestions?

My formula: =INDEX($A$2:$A$7,MATCH($E3,$B$2:$B$7,0))

Sales GP% GP$ Data Series A Lookup Formula
637512.6029 22.8 145352.8735 22 #N/A
638125.1545 23.5 149959.4113 22.1 681463.3977
681463.3977 22.1 150603.4109 22.3 #N/A
513994.8321 21.3 109480.8992 22.4 #N/A
710869.19 22.6 152836.8759 22.5 #N/A
690007.02 23.2 160081.6286 22.6 710869.19

Thanks in anticipation.

Trish

[JMB]

=IF(ISNA(MATCH($E3,$B$2:$B$7,0)),"",INDEX($A$2:$A$7,MATCH($E3,$B$2:$B$7,0)))


"tuph" wrote:

>
> I am using the formula below (adapted from one found on this forum -
> thanks!) and it works as far as it goes. However, I need to have it in
> all cells in column E (it's a data series for a chart), but those fields
> should be left empty if no match is found in B2:B7. Any suggestions?
>
> My formula: =INDEX($A$2:$A$7,MATCH($E3,$B$2:$B$7,0))
>
> Sales GP% GP$ Data Series A Lookup Formula
> 637512.6029 22.8 145352.8735 22 #N/A
> 638125.1545 23.5 149959.4113 22.1 681463.3977
> 681463.3977 22.1 150603.4109 22.3 #N/A
> 513994.8321 21.3 109480.8992 22.4 #N/A
> 710869.19 22.6 152836.8759 22.5 #N/A
> 690007.02 23.2 160081.6286 22.6 710869.19
>
> Thanks in anticipation.
>
> Trish
>
>
> --
> tuph
> ------------------------------------------------------------------------
> tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390
> View this thread: http://www.excelforum.com/showthread...hreadid=565865
>
>

[tuph]

An elegant solution, and it has introduced me to the wonders of IS functions.

Thanks very much!