• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

E

Ecel Dumbo

Member
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


Link for the file


http://speedy.sh/jDaBM/Complicated-Look-UP.xls


Download at SpeedyShare
 
Hi ,


You can use this formula in C2 :


=IF(A2="",INDEX($I$9:$I$18,MATCH(INDEX($G$9:$G$18,MATCH(C1,$I$9:$I$18,0))&B2,$G$9:$G$18&$H$9:$H$18,0)),INDEX($I$9:$I$18,MATCH(A2&B2,$G$9:$G$18&$H$9:$H$18,0)))


Narayan
 
Hi Narayan, As always , you show me light at the end of the tunnel. The formula works great. However I have one request. The file that I had attached is part of a massive file I am working on. Is it possible to select the whole column as array range (in index formula) rather than specific values ? That is selecting I:I instead of $I$9:$I$18


Thanks Narayan. Appreciate your effort.
 
Hi ,


I am not sure , but on the data that is present in your file , even this works :


=IF(A2="",INDEX($I:$I,MATCH(INDEX($G:$G,MATCH(C1,$I:$I,0))&B2,$G:$G&$H:$H,0)),INDEX($I:$I,MATCH(A2&B2,$G:$G&$H:$H,0)))


Narayan
 
Hi Narayan, I tired and it's giving me #NUM error


please see file in the below link


http://speedy.sh/h8Vv6/Complicated-Look-UP.xls


I am using Excel 2003, hence used Ctrl+Shif+Error around your formula.


Thanks
 
Hi ,


The problem is that Excel did give an error message when I was saving the file in Compatibility mode ; can you use a .xlsx file ? If so , download this :


http://speedy.sh/xM3eF/Complicated-Look-UP-1.xlsx


Or , use this formula , and copy down :


=IF(A2="",INDEX($I$2:$I$65536,MATCH(INDEX($G$2:$G$65536,MATCH(C1,$I$2:$I$65536,0))&B2,$G$2:$G$65536&$H$2:$H$65536,0)),INDEX($I$2:$I$65536,MATCH(A2&B2,$G$2:$G$65536&$H$2:$H$65536,0)))


Narayan
 
It works like magic now. I guess i should ditch my Excel 2003 and go for some thing above that version


THanks a lot narayan. U r the best
 
You must log in or register to reply here.
Back
Top