VLOOKUP

[Larry1921]

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.

[#2]

@Larry1921 

That could be

=IFNA(INDEX($D$6:$D$20,MATCH(N18&N19,$A$6:$A$20&$C$6:$C$20,0)),"no such")

Please check attached.

[#3]

@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.

[#4]

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

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

[#5]

Thanks.

[#6]

Thanks