IF conditional with INDEX and MATCH

%3CLINGO-SUB%20id%3D%22lingo-sub-2478049%22%20slang%3D%22en-US%22%3EIF%20conditional%20with%20INDEX%20and%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2478049%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20array%20of%20data%20that%20is%203%20columns%20and%2027%20rows.%20I%20currently%20am%20using%20only%20INDEX%20and%20MATCH%20to%20type%20in%20a%20value%20and%20retrieve%20the%20other%20two%20correlated%20column%20values%2C%20however%20this%20only%20works%20if%20the%20value%20entered%20has%20an%20exact%20match%20in%20the%20designated%20search%20column.%20If%20the%20value%20does%20not%20have%20an%20exact%20match%2C%20I%20need%20the%20code%20to%20interpolate%20the%20values%20of%20the%20other%20two%20columns%20using%20the%20formula%20y2%20%3D%20(((%20x2%20-%20x1%20)%20(%20y3%20-%20y1%20))%20%2F%20(%20x3%20-%20x1%20))%20%2B%20y1.%20Any%20help%20is%20greatly%20appreciated!%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3Ecurrently%20am%20using%26nbsp%3B%3DINDEX(B10%3AE36%2CMATCH(H2%2CE10%3AE36%2C0)%2C1)%20to%20enter%20a%20value%20in%20cell%20H2%20and%20get%20outputs%20from%20the%20same%20row%20but%20different%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2478049%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2478164%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20conditional%20with%20INDEX%20and%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2478164%22%20slang%3D%22en-US%22%3EA%20little%20hard%20to%20visualize%20what%20result%20you're%20looking%20for%20but%20would%20something%20like%20this%20work%3F%3CBR%20%2F%3E%3DIF(B2%26lt%3B%26gt%3BC2%2C(%5Bput%20formula%20here%5D)%2CXLOOKUP(H2%2CA%3AA%2CB%3AC))%3CBR%20%2F%3EIt's%20saying%20if%20B2%20an%20C2%20are%20different%2C%20then%20run%20your%20formula%2C%20otherwise%20return%20the%20two%20values%20in%20B%20and%20C%20from%20the%20lookup%20value%20set%20in%20H2.%20My%20columns%20are%20different%20then%20yours%20but%20the%20logic%20should%20be%20the%20same.%20XLookup%20is%20a%20great%20replacement%20for%20index%20and%20match.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2478291%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20conditional%20with%20INDEX%20and%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2478291%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F993615%22%20target%3D%22_blank%22%3E%40DKoontz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20only%20see%20HLOOKUP%20or%20VLOOKUP%20and%20neither%20would%20be%20suitable%20as%20they%20only%20go%20from%20left%20to%20right%20when%20going%20through%20a%20table%20or%20array.%20The%20data%20isn't%20formatted%20in%20a%20way%20to%20easily%20use%20lookups%20due%20to%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20included%20a%20picture%20of%20how%20I%20need%20to%20input%20and%20output%20data.%20I%20enter%20a%20value%20into%20the%20blue%20box%20and%20get%20the%20correlated%20values%20in%20the%20white%20boxes.%20however%20if%20I%20enter%2015%2C%20for%20example%2C%20but%20there%20is%20only%20data%20correlated%20to%2013%20and%2017%2C%20I%20need%20to%20use%20those%20as%20well%20as%20the%20as%20well%20as%20the%20correlated%20data%20from%20'RAW'%20to%20interpolate%20the%20'RAW'%20value%20at%2015%2C%20and%20then%20do%20the%20same%20for%20'ADC'.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JonathonHempel_0-1624480675985.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F290804iF503E1F445417447%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JonathonHempel_0-1624480675985.png%22%20alt%3D%22JonathonHempel_0-1624480675985.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ethe%20following%20image%20shows%20how%20I'm%20trying%20to%20proceed%20with%20an%20IF%20conditional%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JonathonHempel_1-1624481046679.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F290806iD3E5CBBF2A7C8AEF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JonathonHempel_1-1624481046679.png%22%20alt%3D%22JonathonHempel_1-1624481046679.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ebasically%20I'm%20trying%20to%20code%20'If%20there%20is%20an%20exact%20value%20match%20within%20the%20column%2C%20return%20the%20correlated%20value%20for%20'RAW'%2C%20if%20there's%20not%20an%20exact%20match%2C%20i%20need%20to%20use%20the%20two%20data%20points%20above%20and%20below%20to%20interpolate%20the%20'RAW'%20value%20and%20return%20that.%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20I%20will%20use%20that%20same%20code%20only%20returning%20the%20'ADC'%20values%3C%2FP%3E%3CP%3Ehope%20this%20clarifies%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have an array of data that is 3 columns and 27 rows. I currently am using only INDEX and MATCH to type in a value and retrieve the other two correlated column values, however this only works if the value entered has an exact match in the designated search column. If the value does not have an exact match, I need the code to interpolate the values of the other two columns using the formula y2 = ((( x2 - x1 ) ( y3 - y1 )) / ( x3 - x1 )) + y1. Any help is greatly appreciated!  

currently am using =INDEX(B10:E36,MATCH(H2,E10:E36,0),1) to enter a value in cell H2 and get outputs from the same row but different columns.

2 Replies
A little hard to visualize what result you're looking for but would something like this work?
=IF(B2<>C2,([put formula here]),XLOOKUP(H2,A:A,B:C))
It's saying if B2 an C2 are different, then run your formula, otherwise return the two values in B and C from the lookup value set in H2. My columns are different then yours but the logic should be the same. XLookup is a great replacement for index and match.

@DKoontz 

I only see HLOOKUP or VLOOKUP and neither would be suitable as they only go from left to right when going through a table or array. The data isn't formatted in a way to easily use lookups due to this.

 

I included a picture of how I need to input and output data. I enter a value into the blue box and get the correlated values in the white boxes. however if I enter 15, for example, but there is only data correlated to 13 and 17, I need to use those as well as the as well as the correlated data from 'RAW' to interpolate the 'RAW' value at 15, and then do the same for 'ADC'. 

JonathonHempel_0-1624480675985.png

the following image shows how I'm trying to proceed with an IF conditional;

JonathonHempel_1-1624481046679.png

basically I'm trying to code 'If there is an exact value match within the column, return the correlated value for 'RAW', if there's not an exact match, i need to use the two data points above and below to interpolate the 'RAW' value and return that. 

Then I will use that same code only returning the 'ADC' values

hope this clarifies