CONCATENATE adding quotes

[Major Disaster]

Good afternoon,

I have a long formula that generates a LOOKUP function from varying sources it is looking for the maximum value in Column I then getting the correlated point in column B, to perform the LOOKUP of the timings in column A, this data is a wave form and I need to find it's initial point of crossing a threshold. The formula for the LOOKUP is such

=LOOKUP(">=67",CONCATENATE("B",MATCH((MAX(I9:I450)),$I9:$I450,0)-3,":","B",20+MATCH((MAX(I9:I450)),$I9:$I450,0)),CONCATENATE("A",MATCH((MAX(I9:I450)),$I9:$I450,0)-3,":","A",20+MATCH((MAX(I9:I450)),$I9:$I450,0)
this gives a #VALUE result.

When working step by step through the formula in excel it finally processes down to
Evaluation: = LOOKUP(95.413,"B356:379","A356:379")

the next step fails due to the Quotation marks in the formula. When typing in =LOOKUP(95.413,B356:379,A356:379) the formula works perfectly.

Does anyone know how to remove the quotation marks?

Thanks

 

[Noodleski]

LOOKUP(95.413,indirect("B356:B379"),indirect("A356:A379"))

Is it possible you forgot the column references for the second part of each range? I added them again.

 

[Major Disaster]

My bad when typing, the column references are there in the formula I just mistyped when putting on here.

 

[Major Disaster]

thanks for that the INDIRECT worked perfectly.