VLOOKUP

%3CLINGO-SUB%20id%3D%22lingo-sub-1801301%22%20slang%3D%22en-US%22%3EVLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1801301%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20not%20yet%20familiar%20with%20the%20VLOOKUP%20function.%26nbsp%3B%20I%20want%20to%20create%20a%20formula%20where%20it%20will%20reference%20something%20in%20Column%20A%20(Alphanumeric%20field)%20and%20several%20lines%20in%20Column%20A%20have%20the%20same%20number.%26nbsp%3B%20After%20checking%20for%20my%20number%20in%20column%20A%20then%20I%20want%20to%20reference%20a%20number%20in%20Column%20C%20and%20obtain%20the%20value%20in%20column%20D.%3C%2FP%3E%3CP%3ESo%20I%20place%20the%20number%20that%20I%20want%20to%20reference%20in%20column%20A%20into%20Cell%20N18%20and%20the%20Number%20I%20want%20to%20reference%20for%20Column%20C%20in%20Cell%20N19%20to%20return%20the%20value%20in%20Column%20D.%3C%2FP%3E%3CP%3ECan%20anybody%20give%20me%20a%20formula%20that%20I%20can%20try%3F%26nbsp%3B%20I%20have%20attached%20the%20sample%20spreadsheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1801301%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1801348%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1801348%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F839754%22%20target%3D%22_blank%22%3E%40Larry1921%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFNA(INDEX(%24D%246%3A%24D%2420%2CMATCH(N18%26amp%3BN19%2C%24A%246%3A%24A%2420%26amp%3B%24C%246%3A%24C%2420%2C0))%2C%22no%20such%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPlease%20check%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1802213%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1802213%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F839754%22%20target%3D%22_blank%22%3E%40Larry1921%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ETo%20fix%20the%20issue%20you%20need%20an%20Array%20(CSE)%20formula%2C%20working%20with%20two%20criteria%20are%20in%2C%20cell%20N18%20%26amp%3B%20N19.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ECheck%20the%20attached%20file%20cell%20A20%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%7B%3DIFERROR(INDEX(%24D%246%3A%24D%2416%2CSMALL(IF(COUNTIF(%24N%2419%2C%24A%246%3A%24A%2416)*COUNTIF(%24N%2420%2C%24C%246%3A%24C%2416)%2CROW(%24A%246%3A%24G%2416)-MIN(ROW(%24A%246%3A%24G%2416))%2B1)%2CROW(A1))%2CCOLUMN(A1))%2C%22%22)%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.B.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EFinish%20the%20formula%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%2C%26nbsp%3B%3C%2FSTRONG%3Eand%20fill%20it%20down.%3C%2FLI%3E%3CLI%3EI've%20suggested%20one%20more%20method%2C%20just%20check%20the%20cell%20A26%20in%20attached%20file%2C%2C%20remember%20this%20one%20is%20also%20an%20array%20formula%2C%2C%20also%20fill%20it%20across.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1802253%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1802253%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F839754%22%20target%3D%22_blank%22%3E%40Larry1921%3C%2FA%3E%26nbsp%3BAnother%20variant%2C%20in%20case%20your%20Excel%20version%20recognises%20the%20FILTER%20function.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(D6%3AD16%2C(A6%3AA16%3DN18)*(C6%3AC16%3DN19)%2C%22None%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

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

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

 

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

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

 

Thanks.
Thanks