Vlookup or indexmatch or???

    Hello, can anyone help me? so i've been trying to use VLOOKUP to return a value from a data table from another worksheet, BUT then VLOOKUP returns 0 and pick FIRST MATCH VALUE. I realized why it returns 0 because the source data table has zero/blank values and while VLOOKUP function only pick first match value, i guess (correct me if im wrong).So, what ive been confused about is, What function should i use to return LAST MATCH value from a data table from another worksheet ?


    here i attach sampe file


    i want to replace tht function. I have no idea what function am is supposed to use :( Any idea? Thanks in advance :)

    Hello,


    It is always better to attach a sample file ...


    It allows to quickly see what is going on ... AND it helps proposing a solution fully adapted to your specific situation (instead of getting a general comment .. often not very useful ...:wink:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hello again,


    In fact ... you need to look up for your Numbers from the Bottom Up ... :wink:


    So you need an Array Formula ... since VLookup() and Index-Match() functions will search from the Top to the Bottom ...


    Attached is your file ...


    Hope this will help

    Hello,


    Forgot to tell you that :


    With an Array Formula ... as you type your formula, instead of the standard Enter key,


    You need to use simultaneously the three keys: Control Shift Enter


    Excel will accept your formula by showing curly brackets .. { the formula }


    Hope this will help.

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    i tried to apply the formula to my worksheet but it doesnt work, im trying to figure out what might goes wrong..

    =INDEX(Sheet1!$B$1:$B$20;LARGE(IF(Sheet1!$A$2:$A$20=A2;IF(Sheet1!$B$2:$B$20>0;ROW(Sheet1!$A$2:$A$20)));1))


    can i ask what is that for (the last number (1)) in the formula?

    Hello,


    Have you followed the explanation in Message # 5 ...???


    and


    Do we agree the Test file is showing this formula works as expected ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    YES!! The test file is EXACTLY WHAT I EXPECT THANKYOU :)
    I surely followed your explanation.


    I have no idea what goes wrong but, what if i use column H & C instead of B & A? do i need to change the "k"value ?

    Glad we agree on the fact the test file is working ... !!! :smile:



    Using other columns ... has obviously No impact ... and k has to remain equal to 1 ...


    Can you attach your file with dummy data ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Quote from ItsSugarBunny

    OMG THANKS!! It works like magic <3


    Glad it is working ... as expected ... :smile:


    Thanks for your Thanks ...AND for the Like ...:thumbcoo:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login