Complicated Look Up (without making changes to the blank spaces in the table)

[Excel Dumbo]

Hello Excel Folks,

Please see attached. I need a formula to do a complicated look up. Whe
you open the file, you will notice some blank cells in the table, whic
should remain as it is. Just one formula and should be able to drag i
down
THanks in advance.




Excel Dumb

+-------------------------------------------------------------------
|Filename: Complicated Look UP.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=627
+-------------------------------------------------------------------

 

[Claus Busch]

Hi,

Am Thu, 18 Oct 2012 02:04:17 +0000 schrieb Excel Dumbo:

Please see attached. I need a formula to do a complicated look up. When
you open the file, you will notice some blank cells in the table, which
should remain as it is. Just one formula and should be able to drag it
down

in D2 try:
=INDEX($I$9:$I$18,MATCH(LOOKUP(2,1/($A$1:A2<>""),A:A)&B2,$G$9:$G$18&$H$9:$H$18,0))
and enter the array formula with CTRL+Shift+Enter und copy down

Regards
Claus Busch

 

[Excel Dumbo]

Thanks Claus. That what i wanted. Appreciate ur hel

+-------------------------------------------------------------------
+-------------------------------------------------------------------