Hi,
I have data like this:
and I want to look up an approximate value in Col2, say 310, and return it's next-highest friend from Col3, in this case "g". This is easy enough with INDEX and MATCH. But I want to take it one step further and only use those values which are approved by Col1 - so in this case I want 310 to ACTUALLY return "e"
I found some good information here which gets me close. The following works very well. It uses a boolean & operator to match two values at once, but it only works for exact matches. This example goes down the list and finds the first "sydney" which has an "x" and gives the result "h".
When I use this approach on data like in my first example it falls over, my guess is because the boolean & falls down before the MATCH function has a chance to accept an approximate match.
Has anyone got any ideas on where I could proceed from here?
I have data like this:
Code:
500 a
TRUE 470 b
440 c
TRUE 410 d
TRUE 380 e
350 f
320 g
TRUE 290 h
TRUE 260 i
230 j
TRUE 200 i
TRUE 170 jand I want to look up an approximate value in Col2, say 310, and return it's next-highest friend from Col3, in this case "g". This is easy enough with INDEX and MATCH. But I want to take it one step further and only use those values which are approved by Col1 - so in this case I want 310 to ACTUALLY return "e"
I found some good information here which gets me close. The following works very well. It uses a boolean & operator to match two values at once, but it only works for exact matches. This example goes down the list and finds the first "sydney" which has an "x" and gives the result "h".
Code:
x melbourne a
sydney b
x adelaide c
x melbourne d
sydney e
x adelaide f
x melbourne g
x sydney h
x adelaide i
x melbourne j
x sydney k
x adelaide l
lookup_1 x
lookup_2 sydney
formula INDEX(A1:C12,MATCH(B14&B15,INDEX(A1:A12&B1:B12,),0),3)
result hWhen I use this approach on data like in my first example it falls over, my guess is because the boolean & falls down before the MATCH function has a chance to accept an approximate match.
Has anyone got any ideas on where I could proceed from here?
