Hi there. I am hoping there is someone that can help me with a lookup, index or match formula. I have a spreadsheet with samples numbers. What I want is a formula to look for a sample number within a range in a table and return the new sample number. Example: If the value in A3 is within the range of F3:G31 then give me the new CompID H3:H31 that it goes with. If it does not then I would need it to say "DO NOT USE IN COMPOSITE".
I have a couple formulas that sort of work but for some reason not all the way.
Formula 1 =INDEX($H$3:$H$23,MATCH(1,($F$3:$F$23<=A3)*($G$3:$G$23>=A3),0),)
Formula 2 =LOOKUP(1,1/(($F$3:$F$23<=A25)*($G$3:$G$23>=A25)),$H$3:$H$23)
Any help would be much appreciated!
Thanks!
I have a couple formulas that sort of work but for some reason not all the way.
Formula 1 =INDEX($H$3:$H$23,MATCH(1,($F$3:$F$23<=A3)*($G$3:$G$23>=A3),0),)
Formula 2 =LOOKUP(1,1/(($F$3:$F$23<=A25)*($G$3:$G$23>=A25)),$H$3:$H$23)
Any help would be much appreciated!
Thanks!
| lookup.xlsx | |||
|---|---|---|---|
| C | |||
| 26 | MZ22-01_100-120_COMP | ||
Sheet1 | |||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C26 | C26 | =LOOKUP(2,1/(($F$3:$F$23<=A26)*($G$3:$G$23>=A26)),$H$3:$H$23) |
