vlookup

[soumyajit]

CODES NAMES APPLICATION NOS

461627 ANUKUL DAS C084813671

461627 MANIKA DEBNATH C086512235

461627 LAKSHI RANI SHIL C086512248

461627 JHUTAN DEBNATH H112678019

461627 ANAY CHAKRABORTY C084813685

461627 SANTOSH DAS C084813686


Sir,i have a above mentioned database in sheet1.As you can see the codes are same.I tried my level best to find out all codes related names & application nos with single vlookup formula in sheet2.But i am fail do this.Sir,is there any way to find all the data i want with a single vlookup formula in sheet2.Plz help me out.

 

[nazmul_muneer]

soumyajit,

You can get an extra column in sheet1 that will merge ColumnA and columnB

=A3&A4

In the sheet2 apply same logic.


Regards,


Muneer

 

[bobhc]

Good evening soumyajit


This code is in cell J16, it is entered as an array formula so you must press Ctrl+Shift+Enter to get it to work. If you’re code numbers are in column A3 down and the rest of your data is in B3:E41 then when you enter a code into cell J15 it will pull all the data related to that code (make sure your data is sorted).

When you have entered it into J16 click fill handle and drag right as many instances you have for the code numbers, if you have 6 drag six, then click fill handle at right most cell and drag down for number of cells required for code number details.

Adjust cell references to suit.

If you copy and paste the formula do not copy and paste the chicken lips


{=IFERROR(INDEX($B$3:$E$41, SMALL(IF($A$3:$A$41=$J$15,ROW($A$3:$A$41)-2), COLUMNS($J$16:J16)),ROWS($J$16:J16)), "")}