INDEX & MATCH with INDIRECT Function

I

IDRIS86

Board Regular
Joined
Mar 18, 2021
Messages
82
Office Version
  1. 365
Platform
  1. Windows
Hi I tried to match the argument in the below formula to -1 to pull the higher values than the look up value. But its not working. Only with Match argument 0 & 1 works. But I need to pick value higher than my reference. Could you assist.

Below the function,

=INDEX(INDIRECT(Table10[@MODEL]&"A"),MATCH(BC28,INDIRECT(Table10[@MODEL]&"H"),0),MATCH(BB28,INDIRECT(Table10[@MODEL]&"W"),0))
 
Sort by date Sort by votes
You should not really copy/paste rows in a table as it can cause problems. try deleting that row & let the table expand automatically so the formula fill down.
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Fluff said:
You should not really copy/paste rows in a table as it can cause problems. try deleting that row & let the table expand automatically so the formula fill down.
Click to expand...
Hi Thanks. What should I do if I need to add ten line items to enter the given size input parallely. please advise.
 
Upvote 0
Just enter the relevant values in the 1st 4 cols of your table & if it's set up properly the formula will automatically appear in the last col of the table.
 
Upvote 0
XL2BB does not show us table names.
  1. What is the table name of the small table on Worksheet ?
  2. What is the Table name of the big ("size") table you copied in.
  3. You refer to having multiple tables on the sheet Table.
    What are the Table names of the other Tables ?
    Are they all exactly the same layout ?
  4. If you highlight this part of your formula INDIRECT(Table10[@Model]&"A") and hit F9 what do you get ?
    and do you have a table called that ?
 
Upvote 0
Fluff said:
Just enter the relevant values in the 1st 4 cols of your table & if it's set up properly the formula will automatically appear in the last col of the table.
Click to expand...
I wanted to copy the complete row and copy the same with required number of serials. Could you assist me. I tried copying input side to the number of serials. separately I copied formula side and paste.
 
Upvote 0
Alex Blakenburg said:
XL2BB does not show us table names.
  1. What is the table name of the small table on Worksheet ?
  2. What is the Table name of the big ("size") table you copied in.
  3. You refer to having multiple tables on the sheet Table.
    What are the Table names of the other Tables ?
    Are they all exactly the same layout ?
  4. If you highlight this part of your formula INDIRECT(Table10[@Model]&"A") and hit F9 what do you get ?
    and do you have a table called that ?
Click to expand...
Hi, Thanks for your support.
Below my reply,

1. Small table which has Width height type model value in columns are the actual sheet where I enter input & get the value as output
2. Big table is the actual database from where the input need to looked at pick the value.
3. I removed other tables. All tables layout would be same, where the model, Width, height & values are different. Just I included only one table to see how it works. I named the content or values of every table, let say I named that big table values as IBD2A, horizontal column width named as IBD2W & vertical row height named as IBD2H. Similarly I will name all tables with its model & suffix A for content, W-for width & H for height.
4. This table 10 I believe it refers to the same big table I named IBD2 with its suffix A, W, H as explained above. When I press F9, below the content I can see ,

{52.5,59.0625,62.34375,65.625,68.90625,72.1875,75.46875,82.03125,85.3125,88.59375,91.875,98.4375,420,420,420,420;55.78125,59.0625,62.34375,65.625,72.1875,75.46875,78.75,82.03125,85.3125,91.875,95.15625,98.4375,433.125,433.125,433.125,433.125;59.0625,62.34375,65.625,72.1875,75.46875,78.75,85.3125,88.59375,91.875,98.4375,101.71875,105,459.375,459.375,459.375,459.375;59.0625,65.625,68.90625,72.1875,75.46875,82.03125,85.3125,88.59375,95.15625,98.4375,105,108.28125,472.5,472.5,472.5,472.5;62.34375,68.90625,72.1875,78.75,82.03125,88.59375,91.875,95.15625,101.71875,108.28125,111.5625,114.84375,511.875,511.875,511.875,511.875;65.625,72.1875,75.46875,82.03125,85.3125,91.875,95.15625,101.71875,108.28125,111.5625,118.125,124.6875,538.125,538.125,538.125,538.125;68.90625,75.46875,78.75,85.3125,91.875,95.15625,101.71875,108.28125,111.5625,118.125,124.6875,131.25,564.375,564.375,564.375,564.375;72.1875,78.75,85.3125,91.875,95.15625,101.71875,108.28125,114.84375,118.125,127.96875,131.25,137.8125,603.75,603.75,603.75,603.75;75.46875,82.03125,88.59375,95.15625,101.71875,108.28125,114.84375,121.40625,127.96875,134.53125,141.09375,147.65625,643.125,643.125,643.125,643.125;78.75,85.3125,91.875,98.4375,105,111.5625,118.125,124.6875,131.25,141.09375,147.65625,154.21875,669.375,669.375,669.375,669.375;82.03125,88.59375,95.15625,101.71875,108.28125,114.84375,121.40625,127.96875,134.53125,144.375,150.9375,157.5,682.5,682.5,682.5,682.5;85.3125,91.875,98.4375,105,111.5625,121.40625,127.96875,134.53125,141.09375,150.9375,157.5,164.0625,721.875,721.875,721.875,721.875;88.59375,95.15625,101.71875,111.5625,118.125,124.6875,134.53125,141.09375,147.65625,157.5,164.0625,173.90625,761.25,761.25,761.25,761.25;91.875,98.4375,105,114.84375,121.40625,131.25,137.8125,147.65625,154.21875,164.0625,170.625,180.46875,787.5,787.5,787.5,787.5;95.15625,101.71875,111.5625,118.125,127.96875,134.53125,144.375,150.9375,160.78125,170.625,180.46875,187.03125,826.875,826.875,826.875,826.875;95.15625,105,114.84375,121.40625,131.25,141.09375,147.65625,157.5,167.34375,177.1875,187.03125,193.59375,853.125,853.125,853.125,853.125;98.4375,108.28125,118.125,127.96875,134.53125,144.375,154.21875,164.0625,173.90625,183.75,193.59375,200.15625,879.375,879.375,879.375,879.375;150.9375,164.0625,177.1875,190.3125,203.4375,216.5625,229.6875,242.8125,255.9375,269.0625,282.1875,295.3125,1286.25,1286.25,1286.25,1286.25;150.9375,164.0625,177.1875,190.3125,203.4375,216.5625,229.6875,242.8125,255.9375,269.0625,282.1875,295.3125,1286.25,1286.25,1286.25,1286.25;157.5,170.625,183.75,196.875,210,223.125,236.25,249.375,262.5,282.1875,295.3125,308.4375,1338.75,1338.75,1338.75,1338.75;157.5,170.625,183.75,196.875,210,223.125,236.25,249.375,262.5,282.1875,295.3125,308.4375,1338.75,1338.75,1338.75,1338.75;164.0625,177.1875,190.3125,203.4375,216.5625,229.6875,242.8125,255.9375,269.0625,288.75,301.875,315,1365,1365,1365,1365;164.0625,177.1875,190.3125,203.4375,216.5625,229.6875,242.8125,255.9375,269.0625,288.75,301.875,315,1365,1365,1365,1365;170.625,183.75,196.875,210,223.125,242.8125,255.9375,269.0625,282.1875,301.875,315,328.125,1443.75,1443.75,1443.75,1443.75;170.625,183.75,196.875,210,223.125,242.8125,255.9375,269.0625,282.1875,301.875,315,328.125,1443.75,1443.75,1443.75,1443.75;177.1875,190.3125,203.4375,223.125,236.25,249.375,269.0625,282.1875,295.3125,315,328.125,347.8125,1522.5,1522.5,1522.5,1522.5;177.1875,190.3125,203.4375,223.125,236.25,249.375,269.0625,282.1875,295.3125,315,328.125,347.8125,1522.5,1522.5,1522.5,1522.5;183.75,196.875,210,229.6875,242.8125,262.5,275.625,295.3125,308.4375,328.125,341.25,360.9375,1575,1575,1575,1575;183.75,196.875,210,229.6875,242.8125,262.5,275.625,295.3125,308.4375,328.125,341.25,360.9375,1575,1575,1575,1575;190.3125,203.4375,223.125,236.25,255.9375,269.0625,288.75,301.875,321.5625,341.25,360.9375,374.0625,1653.75,1653.75,1653.75,1653.75;190.3125,203.4375,223.125,236.25,255.9375,269.0625,288.75,301.875,321.5625,341.25,360.9375,374.0625,1653.75,1653.75,1653.75,1653.75;190.3125,210,229.6875,242.8125,262.5,282.1875,295.3125,315,334.6875,354.375,374.0625,387.1875,1706.25,1706.25,1706.25,1706.25;190.3125,210,229.6875,242.8125,262.5,282.1875,295.3125,315,334.6875,354.375,374.0625,387.1875,1706.25,1706.25,1706.25,1706.25;196.875,216.5625,236.25,255.9375,269.0625,288.75,308.4375,328.125,347.8125,367.5,387.1875,400.3125,1758.75,1758.75,1758.75,1758.75;196.875,216.5625,236.25,255.9375,269.0625,288.75,308.4375,328.125,347.8125,367.5,387.1875,400.3125,1758.75,1758.75,1758.75,1758.75;246.09375,265.78125,285.46875,305.15625,324.84375,344.53125,364.21875,383.90625,403.59375,433.125,452.8125,472.5,2047.5,2047.5,2047.5,2047.5}
 
Upvote 0
Alex Blakenburg said:
XL2BB does not show us table names.
  1. What is the table name of the small table on Worksheet ?
  2. What is the Table name of the big ("size") table you copied in.
  3. You refer to having multiple tables on the sheet Table.
    What are the Table names of the other Tables ?
    Are they all exactly the same layout ?
  4. If you highlight this part of your formula INDIRECT(Table10[@Model]&"A") and hit F9 what do you get ?
    and do you have a table called that ?
Click to expand...
Hi,

I just found this formula works. Just replaced MATCH function in my old formula with XMATCH & its argument 1. So it pulled the next higher value from the table if the given width & height is not available. I copy the small table with the formula. But I'm facing another issue. I wanted to copy the complete row into number of serials as required for the customer enquiry. When I just insert rows and copied the row 6 and paste to row 7, it doesnt work and show the values. can you adivse.

Work sheet.xlsx
BCDEF
4WIDTH (MM)HEIGHT (MM)TYPEMODELValue
5
6530508 FD IBD2 135
7530508 FD IBD2 #VALUE!
Work sheet
Cell Formulas
RangeFormula
F6:F7F6=INDEX(INDIRECT(Table10[@MODEL]&"A"),XMATCH(C6,INDIRECT(Table10[@MODEL]&"H"),1),XMATCH(B6,INDIRECT(Table10[@MODEL]&"W"),1))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D7Cell Value=0textNO
E7Cell Value=0textNO
D7Cell Value=0textNO
E7Cell Value=0textNO
F7Cell Value=0textNO
D6Cell Value=0textNO
E6Cell Value=0textNO
D6Cell Value=0textNO
E6Cell Value=0textNO
D5Cell Value=0textNO
E5Cell Value=0textNO
D5Cell Value=0textNO
E5Cell Value=0textNO
F6Cell Value=0textNO
Cells with Data Validation
CellAllowCriteria
D5:D7List=TYPE
E5:E7List=INDIRECT(D5)
 
Upvote 0
We seem to be mixing and matching between table names and range names.
And we are most likely mixing and matching Numerics and Text values as well.

Fixes /assumptions
  1. Work Sheet Width & Height are entered as numbers
    (mind you the formula I have should handle either)
  2. Work Sheet Model has a leading and trailing space.
    You should get rid of the spaces, if you really want to keep it my macro will need to have Trim added to each reference of the model.
  3. I have renamed your Table10 to IBD2A
    Your are most likely going to have to keep the trailing A since IBD2 might be a valid column row reference.
  4. When I converted the data into a table, the column headings can't be numbers so they all became Text values,
    I have used a Trim in the formula against C6 to convert it to text
  5. I have junked your H & W suffixes, they didn't seem to add any value to the way I have done it.
  6. You will need a different table for every product doing it this way, alternatively add a vlookup on the product to get the table name that way.
  7. If I have the H & W the wrong way around you will need to rearrange the Index components, the format is Index(lookup_range, Row, Column)
Let me know how you go.

20210320 Index Match Multiple Tables.xlsx
ABCDEFG
1
2There was a space before & after IBD2, I removed them
3
4WIDTH (MM)HEIGHT (MM)TYPEMODELValue
5
6530508 FD IBD2131.3
7530508 FD IBD2131.3
8
Work sheet
Cell Formulas
RangeFormula
F6:F7F6=INDEX(INDIRECT(E6&"A"&"[#All]"),XMATCH(VALUE(B6),INDIRECT(E6&"A"&"[[#All],[SIZE]]"),1),XMATCH(TRIM(C6),INDIRECT(E6&"A"&"[#Headers]"),1))


20210320 Index Match Multiple Tables.xlsx
ABCDEFGHIJKLMNOPQRSTU
1
2
3
4
5Numeric values
6|
7|
8\/
9
10Text values --->SIZE1021522032543053564064575085596106602900295030003048
1110252.559.162.365.668.972.275.58285.388.691.998.4420420420420
1215255.859.162.365.672.275.578.88285.391.995.298.4433.1433.1433.1433.1
1320359.162.365.672.275.578.885.388.691.998.4102105459.4459.4459.4459.4
1425459.165.668.972.275.58285.388.695.298.4105108472.5472.5472.5472.5
1530562.368.972.278.88288.691.995.2102108112115511.9511.9511.9511.9
1635665.672.275.58285.391.995.2102108112118125538.1538.1538.1538.1
1740668.975.578.885.391.995.2102108112118125131564.4564.4564.4564.4
1845772.278.885.391.995.2102108115118128131138603.8603.8603.8603.8
1950875.58288.695.2102108115121128135141148643.1643.1643.1643.1
2055978.885.391.998.4105112118125131141148154669.4669.4669.4669.4
216108288.695.2102108115121128135144151158682.5682.5682.5682.5
2266085.391.998.4105112121128135141151158164721.9721.9721.9721.9
2371188.695.2102112118125135141148158164174761.3761.3761.3761.3
2476291.998.4105115121131138148154164171180787.5787.5787.5787.5
2581395.2102112118128135144151161171180187826.9826.9826.9826.9
2686495.2105115121131141148158167177187194853.1853.1853.1853.1
2791498.4108118128135144154164174184194200879.4879.4879.4879.4
289501511641771902032172302432562692822951286128612861286
2910001511641771902032172302432562692822951286128612861286
3010501581711841972102232362492632822953081339133913391339
3111001581711841972102232362492632822953081339133913391339
3211501641771902032172302432562692893023151365136513651365
3312001641771902032172302432562692893023151365136513651365
3412501711841972102232432562692823023153281444144414441444
3513001711841972102232432562692823023153281444144414441444
3613501771902032232362492692822953153283481523152315231523
3714001771902032232362492692822953153283481523152315231523
3814501841972102302432632762953083283413611575157515751575
3915001841972102302432632762953083283413611575157515751575
4015501902032232362562692893023223413613741654165416541654
4116001902032232362562692893023223413613741654165416541654
4216501902102302432632822953153353543743871706170617061706
4317001902102302432632822953153353543743871706170617061706
4417501972172362562692893083283483683874001759175917591759
4518001972172362562692893083283483683874001759175917591759
4618292462662853053253453643844044334534732048204820482048
Table
 
Upvote 0
Alex Blakenburg said:
We seem to be mixing and matching between table names and range names.
And we are most likely mixing and matching Numerics and Text values as well.

Fixes /assumptions
  1. Work Sheet Width & Height are entered as numbers
    (mind you the formula I have should handle either)
  2. Work Sheet Model has a leading and trailing space.
    You should get rid of the spaces, if you really want to keep it my macro will need to have Trim added to each reference of the model.
  3. I have renamed your Table10 to IBD2A
    Your are most likely going to have to keep the trailing A since IBD2 might be a valid column row reference.
  4. When I converted the data into a table, the column headings can't be numbers so they all became Text values,
    I have used a Trim in the formula against C6 to convert it to text
  5. I have junked your H & W suffixes, they didn't seem to add any value to the way I have done it.
  6. You will need a different table for every product doing it this way, alternatively add a vlookup on the product to get the table name that way.
  7. If I have the H & W the wrong way around you will need to rearrange the Index components, the format is Index(lookup_range, Row, Column)
Let me know how you go.

20210320 Index Match Multiple Tables.xlsx
ABCDEFG
1
2There was a space before & after IBD2, I removed them
3
4WIDTH (MM)HEIGHT (MM)TYPEMODELValue
5
6530508 FD IBD2131.3
7530508 FD IBD2131.3
8
Work sheet
Cell Formulas
RangeFormula
F6:F7F6=INDEX(INDIRECT(E6&"A"&"[#All]"),XMATCH(VALUE(B6),INDIRECT(E6&"A"&"[[#All],[SIZE]]"),1),XMATCH(TRIM(C6),INDIRECT(E6&"A"&"[#Headers]"),1))


20210320 Index Match Multiple Tables.xlsx
ABCDEFGHIJKLMNOPQRSTU
1
2
3
4
5Numeric values
6|
7|
8\/
9
10Text values --->SIZE1021522032543053564064575085596106602900295030003048
1110252.559.162.365.668.972.275.58285.388.691.998.4420420420420
1215255.859.162.365.672.275.578.88285.391.995.298.4433.1433.1433.1433.1
1320359.162.365.672.275.578.885.388.691.998.4102105459.4459.4459.4459.4
1425459.165.668.972.275.58285.388.695.298.4105108472.5472.5472.5472.5
1530562.368.972.278.88288.691.995.2102108112115511.9511.9511.9511.9
1635665.672.275.58285.391.995.2102108112118125538.1538.1538.1538.1
1740668.975.578.885.391.995.2102108112118125131564.4564.4564.4564.4
1845772.278.885.391.995.2102108115118128131138603.8603.8603.8603.8
1950875.58288.695.2102108115121128135141148643.1643.1643.1643.1
2055978.885.391.998.4105112118125131141148154669.4669.4669.4669.4
216108288.695.2102108115121128135144151158682.5682.5682.5682.5
2266085.391.998.4105112121128135141151158164721.9721.9721.9721.9
2371188.695.2102112118125135141148158164174761.3761.3761.3761.3
2476291.998.4105115121131138148154164171180787.5787.5787.5787.5
2581395.2102112118128135144151161171180187826.9826.9826.9826.9
2686495.2105115121131141148158167177187194853.1853.1853.1853.1
2791498.4108118128135144154164174184194200879.4879.4879.4879.4
289501511641771902032172302432562692822951286128612861286
2910001511641771902032172302432562692822951286128612861286
3010501581711841972102232362492632822953081339133913391339
3111001581711841972102232362492632822953081339133913391339
3211501641771902032172302432562692893023151365136513651365
3312001641771902032172302432562692893023151365136513651365
3412501711841972102232432562692823023153281444144414441444
3513001711841972102232432562692823023153281444144414441444
3613501771902032232362492692822953153283481523152315231523
3714001771902032232362492692822953153283481523152315231523
3814501841972102302432632762953083283413611575157515751575
3915001841972102302432632762953083283413611575157515751575
4015501902032232362562692893023223413613741654165416541654
4116001902032232362562692893023223413613741654165416541654
4216501902102302432632822953153353543743871706170617061706
4317001902102302432632822953153353543743871706170617061706
4417501972172362562692893083283483683874001759175917591759
4518001972172362562692893083283483683874001759175917591759
4618292462662853053253453643844044334534732048204820482048
Table
Click to expand...
Hi, I'm new to this MREXCEL. Could you advise how can i copy your method and implement in my excel. Should I copy only the formula and paste it into my worksheet (sheet name) at value header (F6).
 
Upvote 0
If you are happy to rename your table as IBD2A, then you should be able to just copy the F6 formula to your F6
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Index, Indirect, Match query
Replies
1
Views
123
Fluff
Fluff
S
  • Solved
Indirect Formula
Replies
3
Views
198
Fluff
Fluff
S
  • Solved
Need to delete color and no data after blank column
Replies
2
Views
261
sksanjeev786
S
L
  • Solved
Using FILTER in INDIRECT to calculate row value
Replies
1
Views
282
Ludwig
L
P
  • Question
Summing Data based on a count of prior month data
2
Replies
12
Views
733
KRice
K

Forum statistics

Threads
1,215,972
Messages
6,128,014
Members
449,414
Latest member
sameri

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top