• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index & match for Table

anand_g1987

New Member
Hi,
I am a new member to this forum.
i need a help with excel formula related to Index & match & i have uploaded the excel file.
in this both X axis & y axis are variable & chart has just nearest whole number. i tried to put index and match formula but its taking next cell. i need like this if i enter thickness = 1.65 it should take 1.8 & if i enter 255, it should take 300.(check the attached file)
also width above 500 mm should take last column & width below 100 mm should take 1st column. please help.
Thanks in advance
 

Attachments

  • Packing Std.xlsx
    17.7 KB · Views: 7
Alternative with the intersect operator (space) and named ranges.

1. Label columns/rows so they are accepted in the name manager (see left example)
2. Select full range
3. On formula ribbon, select Name manager, create from range
4. I used data validations drop downs in C34 and C35
5. C36:=INDIRECT(C34) INDIRECT(C35)

EDIT: you want approximate match, I see now... Back to drawing table. :)
 

Attachments

  • Copy of Packing Std.xlsx
    19.5 KB · Views: 6
Alternative with the intersect operator (space) and named ranges.

1. Label columns/rows so they are accepted in the name manager (see left example)
2. Select full range
3. On formula ribbon, select Name manager, create from range
4. I used data validations drop downs in C34 and C35
5. C36:=INDIRECT(C34) INDIRECT(C35)

EDIT: you want approximate match, I see now... Back to drawing table. :)

Thanks for the formula. but my values both Thickness & width are variable .
for example: Thickness = 1.25 & width = 355 answer would be pattern 2 i.e. cell i15. i need a formula for this.
 

Attachments

  • Copy of Packing Std.xlsx
    19.1 KB · Views: 4
Hi, I noticed when I answered, what you were looking for was different.
Perhaps this one works better.

Helper column (A7:A30)/row(C5:L5) added for approximate lookup values.
Lookup formula:
=INDEX($C$7:$L$30,MATCH(F34,$A$7:$A$30,1);MATCH(F35,$C$5:$L$5,1))
 

Attachments

  • Copy of Copy of Packing Std.xlsx
    19.7 KB · Views: 5
Another formula solution, without helper column & helper row.

1] Select "Table type" in D34 dropdown list

2] Enter criteria for "Thickness" and "Width" in cell D35 and D36

3] In "Packing Std" D39, array formula (Ctrl+Shift+Enter) :

=INDEX(($C$7:$L$30,$O$7:$X$30),MATCH($D$35,IFERROR(0+MID($B$6:$B$29,7,3),0)+0.009),MATCH($D$36,IFERROR(0+MID($B$6:$K$6,7,3),0)+0.01),IF($D$34="F/G",1,2))

Regards
Bosco
 

Attachments

  • Packing Std(A).xlsx
    23.3 KB · Views: 8
Last edited:
Hi, I noticed when I answered, what you were looking for was different.
Perhaps this one works better.

Helper column (A7:A30)/row(C5:L5) added for approximate lookup values.
Lookup formula:
=INDEX($C$7:$L$30,MATCH(F34,$A$7:$A$30,1);MATCH(F35,$C$5:$L$5,1))

hi,
This is the same formula i used in the beginning. see there is two loop holes.
Header values C5:L5 are the maximum values, say if width 199 then it falls under E5 and if it is 201 then it falls under F5. same goes for thickness also
if 1.21 then it falls under A15 and if it is 1.19 then it falls under A14.
i have given some example also in the attachment.

please help, iam near to deadline and all is depending on this one formula.
i hope you understand my needy. thanks
 

Attachments

  • Copy of Copy of Packing Std.xlsx
    20.2 KB · Views: 4
Another formula solution, without helper column & helper row.

1] Select "Table type" in D34 dropdown list

2] Enter criteria for "Thickness" and "Width" in cell D35 and D36

3] In "Packing Std" D39, array formula (Ctrl+Shift+Enter) :

=INDEX(($C$7:$L$30,$O$7:$X$30),MATCH($D$35,IFERROR(0+MID($B$6:$B$29,7,3),0)+0.009),MATCH($D$36,IFERROR(0+MID($B$6:$K$6,7,3),0)+0.01),IF($D$34="F/G",1,2))

Regards
Bosco
Hi Bosco

super super thanks a lot . its precisely match my requirement. thanks a lot again. can u please explain how this formula works.
 
Back
Top