Forum Discussion

Larry1921's avatar
Larry1921
Copper Contributor
Oct 20, 2020

VLOOKUP

I am not yet familiar with the VLOOKUP function.  I want to create a formula where it will reference something in Column A (Alphanumeric field) and several lines in Column A have the same number.  After checking for my number in column A then I want to reference a number in Column C and obtain the value in column D.

So I place the number that I want to reference in column A into Cell N18 and the Number I want to reference for Column C in Cell N19 to return the value in Column D.

Can anybody give me a formula that I can try?  I have attached the sample spreadsheet.

excel

5 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Oct 21, 2020

    Larry1921 Another variant, in case your Excel version recognises the FILTER function.

    =FILTER(D6:D16,(A6:A16=N18)*(C6:C16=N19),"None")

     

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    Oct 21, 2020

    Larry1921 

     

    To fix the issue you need an Array (CSE) formula, working with two criteria are in, cell N18 & N19.

     

    Check the attached file cell A20:

     

    {=IFERROR(INDEX($D$6:$D$16,SMALL(IF(COUNTIF($N$19,$A$6:$A$16)*COUNTIF($N$20,$C$6:$C$16),ROW($A$6:$G$16)-MIN(ROW($A$6:$G$16))+1),ROW(A1)),COLUMN(A1)),"")}

     

     

    N.B. 

    • Finish the formula with Ctrl+Shift+Enter, and fill it down.
    • I've suggested one more method, just check the cell A26 in attached file,, remember this one is also an array formula,, also fill it across.

     

Resources