How to search tables using the lower bounds

[brianaclark1967]

Hi,

I've attached a table that I need help with the search formula.

Many thanks

Brian

 

[NARAYANK991]

Hi Brian ,

Using the MATCH function with the default value of 1 for the 3rd parameter should work.

See if this works :

=IF(OR(C5=0,C6=0),0,INDEX(table1!$C$4:$H$14,MATCH(C5,table1!$B$4:$B$14),MATCH(C6,table1!$C$3:$H$3)))

Narayan

 

[brianaclark1967]

Hi Brian ,

Using the MATCH function with the default value of 1 for the 3rd parameter should work.

See if this works :

=IF(OR(C5=0,C6=0),0,INDEX(table1!$C$4:$H$14,MATCH(C5,table1!$B$4:$B$14),MATCH(C6,table1!$C$3:$H$3)))

Narayan

Hi Narayan,

Thanks for your reply,

I tried the formula but couldn't get it to pull through an answer.

I'm a bit confused about the default value of 1 for the 3rd parameter to. What is this referring to?

Many thanks,

Brian

 

[NARAYANK991]

Hi Brian ,

See the file now.

The MATCH function has 3 parameters ; the first one is a value which will be looked up to see if there is a match for it in the data range which is the second parameter. The third parameter can take on the values -1 , 0 , 1. If it is omitted , Excel gives it a default value of 1.

The Excel help on the MATCH function explains everything.

Narayan

 

[brianaclark1967]

Hi Brian ,

See the file now.

The MATCH function has 3 parameters ; the first one is a value which will be looked up to see if there is a match for it in the data range which is the second parameter. The third parameter can take on the values -1 , 0 , 1. If it is omitted , Excel gives it a default value of 1.

The Excel help on the MATCH function explains everything.

Narayan

Hi Narayan,

I understand now.

Thank you so much for your help.

You guys on this forum are a really great help. and are very much appreciated by all.

Kind regards.

Brian

 

[Mahir Yagimli]

Hi Brian,

What about your first Question? Is that solved aswell.

"'I'd like a formula that searches table 1 if "C4 = Single Door", table 2 if "C4 = Leaf & Half Door", and table 3 if "C4 = Double Door""

Narayan helped me out to fetch data from different sheets with the help of "INDIRECT".

http://chandoo.org/forum/threads/trimming-array-formulas-and-circular-references.20663/

Maybe you'll find some hints there, to solve your first Question.

Have a nice day!

Mahir

 

[Mahir Yagimli]

Hi Brian,

All of sudden I had the idea to put a CHOOSE Formula into the Formula suggested by Narayan.

You can see the result in the file I have uploaded.

The trick is to first name your tables with the help of the namebox in the upperleft corner of your sheet. e.g. table1 for table1!$C$4:$H$14.

Then name the ranges for your MATCH FORMULAS. Since you will have two of the MATCH FORMULAS in a two way lookup it will be helpful to define the range of rows for the MATCH FORMULA,which will return the row_num in the INDEX Formula. Next would be to define the range of columns for the MATCH formula that returns the column_num in the INDEX Function.

Now you need a Number indicator that changes with you Data Validation list. To solve this I have put a MATCH formula in D4, which is like the one below:

=MATCH(C4,RANGE OF DATA VALIDATION LIST,0)

Now the CHOOSE Formula:

=CHOOSE(D4,table1,table2,table3)

That's basically how you apply the CHOOSE Function for your request.

The Final Formula is:

=IF(AND(C5>0,C6>0),INDEX(CHOOSE(D4,table1,table2,table3),MATCH(C5,CHOOSE(D4,Width_table1,Width_table2,Width_table3)),MATCH(C6,CHOOSE(D4,table1_header,table2_header,table3_header))),0)

Hope my long explanations somehow helped a bit to solve your question.

Mahir