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

Hello Excel Folks,

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
THanks in advance.

Excel Dumbo

Complicated Look UP.xlsx

Not really complicated.

Thanks & Warm Regards,

Prasad Avasare

Thanks for your reply Prasad. The file I have give is a smaller version of the massive file I am working on. I need one formula that I can drag down. Your formula does not give me correct values if i drag down the . Also with regards to array range in the formula, I would like to select the whole column instead of a specific range. Please advise.

In C2: =LOOKUP(2,1/((G$9:G$18=LOOKUP("zzz",A$2:A2))*(H$9:H$18=B2)),I$9:I$18)

copy down as far as needed

thanks teethless mama, the formula worked well. as i have said earlier, is it possible to select array as the whole column instead of specified cells, that, G:G instead of G$9:G$18. the file i had attached is a miniature version of a maaive file i am working on

Originally Posted by Excel Dumbo

thanks teethless mama, the formula worked well. as i have said earlier, is it possible to select array as the whole column instead of specified cells, that, G:G instead of G$9:G$18. the file i had attached is a miniature version of a maaive file i am working on

Yes, it possible. Try it

HI Teethless Mama,

I tried. It's giving me #NUM error

=LOOKUP(2,1/((G:G=LOOKUP("zzz",A$2:A2))*(H:H=B2)),I:I)

PLease see attached

Thanks

Thaks a lot Teethless Mama. This is what i wanted

You're Welcome!