Index match modification

**penguintar** · 11-16-2011, 06:34 AM

I would greatly appreciate some help tweaking the below formula. The formula works great but is restricted to the specific values within the datasheet ranges. Is it possible to add to the formula or tweak it so that if I input a value that was not contained in the data ranges it would automatically input the data contained in cells: Costings!Z1:Z100

=INDEX(PipeOD!$B$3:$B$810,LOOKUP(9.9E+307,CHOOSE({1,2},MATCH(G6,PipeOD!$A$3:$A$810,0),MATCH(G6,PipeO D!$B$3:$B$810,0),)))

My excel skills suck so any help is appreciated!!!

**NBVC** · 11-16-2011, 08:48 AM

I am not exactly sure what you mean by:

if I input a value that was not contained in the data ranges it would automatically input the data contained in cells: Costings!Z1:Z100

So if the formula above errors out you want to invoke an entirely different lookup formula?

Can you elaborate?

**penguintar** · 11-17-2011, 05:15 AM

Yes that is correct. The above formula references sheet 'PipeOD'. Currently on sheet 'Costings' I can enter either one of two data formats and the formula will search the data contained in sheet 'PipeOD' and enter the value in column B.

E.g. If I enter either 2" or just 50.8mm in sheet 'Costings', another column also in 'Costings' will be populated by 50.8mm automatically. There is a data set in sheet 'PipeOD' that is being referenced, however if I enter a figure that is not contained within that dataset it just displays error. Basically what I want is for the formula to automatically enter the figure contained in cell G6 (the original data entry cell where I put 2" or 50.8mm) in the event of error.

This is a bit confusing, but thanks.