Find a certain value in a column and check that row for &quo

[Remaniak]

Hi All,

I would like to do the following:

I am in sheet "Analysis", and I have in cell C23 the value "lot 1".

What I need, is a formula in cell D23 that checks in sheet "data" (range $B$9:$AA$81) in column B for the value of cell C23, namely "lot 1". If it find that value, it has to check within that row for the value "1". If it finds the value "1", in say column G and row 40, it should provide the value of two columns back, thus column D row 40.

Does anyone have a none VB solution for this?

Thanks a lot!!!

Remaniak

 

[Aladin Akyurek]

=INDEX($C$9:$AA$81,MATCH("lot 1",$B$9:$B$81,0),MATCH(1,INDEX($C$9:$AA$81,MATCH("lot 1",$B$9:$B$81,0),0),0)-2)

 

[Remaniak]

Great Once Again!

Thanks a bunch!

Michiel