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.  Af...
excel
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