excel magic trick 778, example 6

T

tpuck2687

New Member
Joined
Dec 12, 2013
Messages
20
Excel Magic Trick 778: INDEX & MATCH Lookup Functions Beginning To Advanced (18 Examples) - YouTube example 6. I have a table similar to this one without the descending numbers as "pipe size". My "pipe sizes" would have to be 0-2, 2.1-4, 4.1-6, 6.1-8, and >8.1. My rainfall would also have to be ranges 0-1000, 1000-2000, 2000-
3000, 3000-4000, and 4000-5000. The corresponding table values are......

1000200030004000
5000
2107541
475
431
6543
21
84
3221
100
11111
<colgroup><col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;" span="6"> <tbody> </tbody>

What value do I need to put in the 100 spot to show anything greater than 8?
if c51=5 and c52=2650 I would need c53=3 and I'm currently getting 5 with...
=INDEX(Sheet2!$G$5:$K$9,MATCH(Schaurer!C51,Sheet2!$F$5:$F$9,1),MATCH(Schaurer!C52,Sheet2!$G$4:$K$4,1))
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Hi, try with data as follows:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; vertical-align: middle; white-space: normal; }.xl64 { color: black; font-weight: 700; font-family: Calibri; text-align: center; vertical-align: middle; background: none repeat scroll 0% 0% rgb(217, 217, 217); white-space: normal; }</style>
01000200030004000
0107541
275431
454321
643221
811111

<tbody>
</tbody>
 
Upvote 0
That worked! Thanks! I see what it was trying to do and why I was a column off. Is there any way to show that range? ie.....0-2, 2-4 etc
 
Upvote 0
tpuck2687 said:
That worked! Thanks! I see what it was trying to do and why I was a column off. Is there any way to show that range? ie.....0-2, 2-4 etc
Click to expand...

given <style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { vertical-align: middle; white-space: normal; }</style>
0to2
2to4
4to6
6to8
8to∞

<colgroup><col style="width:65pt" width="65"> </colgroup><tbody>
</tbody>

formula would be =INDEX(Sheet2!$G$5:$K$9,MATCH(Schaurer!C51,LEFT(Sheet2!$F$5:$F$9,1)*1,1),MATCH(Schaurer!C52,Sheet2!$G$4:$K$4,1)) Ctrl + Shift + Enter, not just enter on a PC or Command + Return on a MAC.
 
Upvote 0
formula would be =INDEX(Sheet2!$G$5:$K$9,MATCH(Schaurer!C51,LEFT(Sheet2!$F$5:$F$9,1)*1,1),MATCH(Schaurer!C52,Sheet2!$G$4:$K$4,1)) Ctrl + Shift + Enter, not just enter on a PC or Command + Return on a MAC.[/QUOTE]


Would that also work with ranges on the top as well, or would I have to change something else in the formula? ie 0to1000, 1000to2000 etc
 
Upvote 0
Given
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; vertical-align: middle; white-space: normal; }</style>
0to10001000to20002000to30003000to40004000to∞
0to2107541
2to475431
4to654321
6to843221
8to∞11111

<tbody>
</tbody>

Adjust formula to =INDEX(Sheet2!$G$5:$K$9,MATCH(Schaurer!C51,LEFT(Sheet2!$F$5:$F$9,1)*1,1),MATCH(Schaurer!C52/1000,LEFT(Sheet2!$G$4:$K$4,1)*1,1)) Ctrl + Shift + Enter, not just enter on a PC or Command + Return on a MAC


Would that be close to what you required?
 
Upvote 0
That's exactly what I needed! Some people just don't see the ranges unless I mark them accordingly, so I had to put them in. Thanks for the help!!
 
Upvote 0
I had a chance this afternoon to input some data, one of the customers is 8 and 1167. I need that to fall as a 3 and its coming up with 1. There is another one with 8 that also seems to be on the wrong row......not sure what the deal is. everything else seems good
 
Upvote 0
tpuck2687 said:
I had a chance this afternoon to input some data, one of the customers is 8 and 1167. I need that to fall as a 3 and its coming up with 1. There is another one with 8 that also seems to be on the wrong row......not sure what the deal is. everything else seems good
Click to expand...

Hi, merry Christmas. Have you been able to duplicate your concern with this specific data? or is it working now?
 
Upvote 0
Nothing has changed. I tried changing the ranges to 0to2 2.1to4 4.1to6 6.1to8 and 8.1to∞ to see if that would work and it didn't seem to help
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Index and match formula that ignores dynamic value
2
Replies
14
Views
391
FormR
FormR
P
  • Solved
Creating a find, match, lookup, and return formula
Replies
2
Views
169
Eric W
Eric W
Knockoutpie
  • Solved
Transpose cells in a column if adjacent criteria matches
Replies
5
Views
97
Fluff
Fluff
V
  • Question
Match , Index and Indirect
Replies
5
Views
266
vmjan02
V
J
  • Question
Index Match and Arrays
Replies
1
Views
424
Herakles
H

Forum statistics

Threads
1,214,827
Messages
6,121,806
Members
449,048
Latest member
greyangel23

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