Vlookup question

[nader assaleh]

hi
my question is a simple one. in vlookup function
range_lookup: Optional. If TRUE or omitted, an approximate match is returned.
(If an exact match is not found, the next largest value that is less than lookup_
value is returned.). is there a way to let vlookup choose two values that is the value that is
less than lookup value and the value that is greater than lookup value and interpolate them

in the attached file if the quantity is 63 then vlookup function will give me price as 20
the result im looking for is

20+18=38 then 38/2=19

thank you much

 

[Luke M]

To do what your wrote as an example, formula would be:
=AVERAGE(INDEX(D5:D15,MATCH(63,C5:C15)),INDEX(D5:D15,MATCH(63,C5:C15)+1))
Note that this is not a True linear interpolation. See attached formula for comparison.

 

[nader assaleh]

HI LUKE
thank you much this what i want exactly but i couldnt implement it in real scenario.
kindly if you help me to write the proper formula

i have many table in vertical axis compromise of quantity of fuel and in horizontal axis fuel density
then based aircraft type and quantity of fuel and fuel density (intersection point) we find the fuel index.

e.g
aircraft registration jyeme (aircraft type e95)
if fuel amount 2000 and fuel density .78 we get value as 997 (vlookup method)
can you please help me to write using
Interpolation, using named ranges

 

[Luke M]

Oh yes, I remember this setup. I believe we helped you with some Data validation on this setup.

For this question, I'm not sure how to read your table and interpolate. AC tells me what table, Weight is given, density is column. Are we interpolating Weight? If so, I don't think we need to, since the smallest change from 1 row to next is an increment of 1.

 

[nader assaleh]

no i am interpolating the fuel effect (index)

a/c jyeme
fuel amount 1800
fuel density .78

if a/c reg. is jyeme then the formula should from table 1 we find that the aircraft type is e95
from e95 table find the value as 997 (vlookup)

sorry for poor English langguage

 

[Luke M]

No apologies needed, I understand.

However, looking at your table, the Index at 1500 is 998, and the Index at 2000 is 997. This is only an increment of 1, so I don't think you need to interpolate. Instead, you could round the fuel amount to the nearest 500, and lookup that value (unless you need 2 decimal precision).
=MROUND(1800,500)

 

[nader assaleh]

this almost the last puzzle to be solved to get my project complete. im trying to mimick established software in producing loadsheet (weight and balance sheet) in which we interpolate the fuel value (these values sometimes change by more than 5 index from one interval into the other based on aircraft type and quantity of fuel so it is an essential to linealy interpolate it . so it is of same logic as the first question i asked but in more complicated situation

 

[SirJB7]

Hi, nader assaleh!

For the quantity price example try this formula:

D6: =SI.ERROR(BUSCARV(D5;DATA!C5:D15;2;FALSO);SI(ESNOD(INDICE(DATA!D5:D15;COINCIDIR(D5;DATA!C5:C15;1)));"wtf";SI(COINCIDIR(D5;DATA!C5:C15;1)<FILAS(DATA!C5:C15);(INDICE(DATA!D5:D15;COINCIDIR(D5;DATA!C5:C15;1))+INDICE(DATA!D5:D15;COINCIDIR(D5;DATA!C5:C15;1)+1))/2;INDICE(DATA!D5:D15;COINCIDIR(D5;DATA!C5:C15;1))))) -----> in english: =IFERROR(VLOOKUP(D5,DATA!C5:D15,2,FALSE),IF(ISNA(INDEX(DATA!D5:D15,MATCH(D5,DATA!C5:C15,1))),"wtf",IF(MATCH(D5,DATA!C5:C15,1)<ROWS(DATA!C5:C15),(INDEX(DATA!D5:D15,MATCH(D5,DATA!C5:C15,1))+INDEX(DATA!D5:D15,MATCH(D5,DATA!C5:C15,1)+1))/2,INDEX(DATA!D5:D15,MATCH(D5,DATA!C5:C15,1)))))

Where the formula structure is as follows, so as to make it easier for you to adapt to the fuel example case:

=IFERROR(VLOOKUP(D5,DATA!C5:D15,2,FALSE),
                  IF(ISNA(INDEX(DATA!D5:D15,MATCH(D5,DATA!C5:C15,1))),
                        "wtf",
                        IF(MATCH(D5,DATA!C5:C15,1)<ROWS(DATA!C5:C15),
                            (INDEX(DATA!D5:D15,MATCH(D5,DATA!C5:C15,1))+INDEX(DATA!D5:D15,MATCH(D5,DATA!C5:C15,1)+1))/2,
                            INDEX(DATA!D5:D15,MATCH(D5,DATA!C5:C15,1))
                        )
                    )
  )

If exists exact value then
    take its 2nd column
else
    if don't exists a lower value then
        what the fu... el? ;)
    else
        if it isn't in the last threshold then
            take the average of it and the next one
        else
            take it only
        endif
    endif
endif

Just advise if any issue.

Regards!

 

[Debraj]

Hi Nader,

I would like suggest your to create a similar table for all.. It will help you to in further analysis, (and in previous all queries also.. )

Just check the attached..
I have changed the structure of your lookup table. Try to adapt it..

Now check how it will help you..

Index's 1st parameter is highly customizable.. You can give multiple table in the same area, and after lookup, you cna choose, from which table you need to lookup.

something like =INDEX((Table1,table2),3,4,2)
it will return results from table number 2.

Check the attached for more detail.

=INDEX(('DATA TWO TABLES'!B4:J49,'DATA TWO TABLES'!L4:T49),MATCH(DATA!N5,'DATA TWO TABLES'!B4:B49),MATCH(DATA!N6,'DATA TWO TABLES'!B4:J4,0),VLOOKUP(VLOOKUP(N4,U4:V13,2,0),{"e95",1;"a321",2},2,0))

 

[nader assaleh]

hi again
can i apply interpolation to the following situation (i did try many many times but i couldnt)

AIRCRAFT REGISTRATION
JYAIA
FUEL QUANTITY 28400
FUEL DENSITY 3.08

THEN FUEL INDEX IS -21

i want to find way to let excel take the next fuel quantity from the table (29000)
and get its fuel index value -24 and interpolate them....
thank you nuch

 

[Debraj]

Hi Nader,

Did not able to test it properly..

Can you please test and confirm if its working for you...

=FORECAST(B4,OFFSET(INDIRECT(VLOOKUP($B$3,DATA,4,0)),MATCH(B4,INDEX(INDIRECT(VLOOKUP($B$3,DATA,4,0)),0,1),1)-1,MATCH($B$5,INDEX(INDIRECT(VLOOKUP($B$3,DATA,4,0)),1,0),1)-1,2,1),OFFSET(INDIRECT(VLOOKUP($B$3,DATA,4,0)),MATCH(B4,INDEX(INDIRECT(VLOOKUP($B$3,DATA,4,0)),0,1),1)-1,0,2,1))

 

[nader assaleh]

dear Debraj
thank you very much formula works great except in one situation e.g if we choose the maximum fuel quantity (last cell in any fuel table) it give #DIV/0!.

any suggestions

 

[nader assaleh]

thank you much. sorted out.

 

[Debraj]

Hi nader..

Glad that it worked for you..

Can you please share the final stage.. so user who reads this.. will helped..

 

[nader assaleh]

Hi Debraj ..
final formula is

=IFERROR(FORECAST(B4,OFFSET(INDIRECT(VLOOKUP($B$3,DATA,4,0)),MATCH(B4,INDEX(INDIRECT(VLOOKUP($B$3,DATA,4,0)),0,1),1)-1,MATCH($B$5,INDEX(INDIRECT(VLOOKUP($B$3,DATA,4,0)),1,0),1)-1,2,1),OFFSET(INDIRECT(VLOOKUP($B$3,DATA,4,0)),MATCH(B4,INDEX(INDIRECT(VLOOKUP($B$3,DATA,4,0)),0,1),1)-1,0,2,1)),VLOOKUP($B$4,INDIRECT(VLOOKUP($B$3,DATA,4,0)),MATCH($B$5,INDEX(INDIRECT(VLOOKUP($B$3,DATA,4,0)),1,0),1),1))