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

5 Replies

# Re: VLOOKUP

That could be

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

# Re: VLOOKUP

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.

# Re: VLOOKUP

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

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

Thanks.

Thanks