Using Lookup Function to find a cell with no corresponding lookup value

[gaurav99]

Hi All - this is my first post here. I have followed this forum and have learned immensely.

My problem: (attached file for reference)

I am looking up data in table#1 (cell B5) from table#2 and trying to retrieve data (cell I6).

I can reach column I using a normal vlookup function, but confused as to how to tell excel to go down in column I to find cell I6.

Table#1 has over 100 unique values thus a dynamic (copy/paste) formulae would help..

Many thanks in advance..

 

[Hui]

C8: =SUM(OFFSET($E$7:$E$9,,4*(1+INT(MATCH($B8,$F$5:$U$5,0)/4))))
Copy down

 

[gaurav99]

C8: =SUM(OFFSET($E$7:$E$9,,4*(1+INT(MATCH($B8,$F$5:$U$5,0)/4))))
Copy down

thanks a ton Hui.. the solution sums the values present in column I where as i only want value in last cell (i.e. I6).. there may be other data present in column I which should not be considered.. Please suggest

 

[Somendra Misra]

Hi Gaurav,

Try below formula in C8 and copy down:

=OFFSET($F$5,MATCH(LOOKUP(9.999999E+307,INDEX($F$7:$U$9,,MATCH($B8,$F$5:$U$5,0)+3)),INDEX($F$7:$U$9,,MATCH($B8,$F$5:$U$5,0)+3),0)+1,MATCH($B8,$F$5:$U$5,0)+2)

Change the range marked in RED as per your Table2.

Regards,

 

[gaurav99]

Thanks Somendra..works like a charm.. thanks again

 

[Somendra Misra]

You are Welcome!!!

Regards,