-1

I would like to use a function to reference my INPUT against the appropriate values from the left-hand columns and return the appropriate OUTPUT using the values in the right-hand columns.

Screenshot from my spreadheet

In the screenshot, I'm using the INPUT of "1200" in cell G7. I'd like to enter the function to lookup "1200" in cells A2:A8 and return a value of "3/0" from the range C2:C8.

Examples of what I'd like to see:

INPUT: 1200, OUTPUT: 3/0
INPUT: 3,    OUTPUT: 8
INPUT: 4/0,  OUTPUT: 2

I can enter a function to perform what I want for one specific instance, but I'm lost when it comes to programming this function to work with all instances in my table.

*Note: In the examples, I'm only using the columns "Copper-IN" and "Copper-OUT".

David Makogon
  • 69,407
  • 21
  • 141
  • 189
user7224270
  • 1
  • You will need some sort of custom function to parse your ranges of values in column A. None of the Excel built-in functions will be able to determine that a value falls within your "range" of values. – David Zemens Nov 29 '16 at 17:00
  • *I can enter a function to perform what I want for one specific instance* It would be very helpful if you showed us what this function looks like, it may be possible to adapt it for more universal application. – David Zemens Nov 29 '16 at 17:02
  • Also unclear what would be the output for an input value of `1` (and why), since that seems to match range criteria in Cell A2 and also in A3 – David Zemens Nov 29 '16 at 17:06

2 Answers2

1

It's hard to understand this problem without knowing what the 2/0 etc codes mean.

I suggest you convert those codes to numeric values if that's possible. Then in your Copper-IN and Aluminium-IN columns use only numeric values, sorted in increasing order (low at top).

Then, using Index-Match, you can easily look up the output.

=Index(CopperIn,Match(D8,CopperOut))

(or =Index($A$2:$A$5,Match(D8,$B$2:$B$5)) without naming the ranges.)

Demonstration of output

Jack Deeth
  • 3,062
  • 3
  • 24
  • 39
0

Under ordinary circumstances you should be able to use a VLOOKUP for this:

=VLOOKUP(G7, $A$2:$C$8, 3, False)

This looks for the value of G7 in the range A2:C8 and returns the corresponding value from the 3rd column (VLOOKUP is always a left-to-right, and always indexes against the first column in the range/array).

Alternatively, combining Index and Match functions:

=Index($C$2:$C$8, Match(G7, $A$2:$A$8, 0))

This says basically the same thing: Return the value in C2:C8 from the row which matches the row where G7's value is found in A2:A8. Combining Index/Match is useful if you are using non-contiguous ranges or require a right-to-left search, etc.

Both functions will return an error value of the searched value from G7 is not found in column A. You can wrap either of the above with the IfError function to give a custom error text, such as "Not found".

=IFERROR(VLOOKUP(G7, $A$2:$C$8, 3, False), G7&" isn't found!")

However

Your column A contains text "ranges" (e.g., > 350 - 600, etc.) and none of the built-in functions will be able to parse that. Further complicating the issue is that you have some values in column A which aren't easily parsed ("2 or smaller", "1 or 1/0", etc.).

I can enter a function to perform what I want for one specific instance

It would be very helpful if you showed us what this function looks like, it may be possible to adapt it for more universal application.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • 2
    I don't think this would actually work ... right now, the columns are set up as ranges and that means they're probably text. VLOOKUP, INDEX, and LOOKUP will all return errors because the input "1200" is never actually found. – acousticismX Nov 29 '16 at 16:44
  • @acousticismX good point, you know what I didn't even look at the actual data in OP's image because it wasn't included in the original post until later. You're right, though, there isn't a way to do this using text "ranges" of values... or, if there is, it's not as simple as the answer I have above. – David Zemens Nov 29 '16 at 16:59