|
Lookup to the Left
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 |
Lookup to the Left
=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 |
Lookup to the Left
An elegant solution, and it has introduced me to the wonders of IS functions. Thanks very much! -- tuph ------------------------------------------------------------------------ tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390 View this thread: http://www.excelforum.com/showthread...hreadid=565865 |
| All times are GMT +1. The time now is 07:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com