Using Vlookup or Index/Match to get results for range of cells

[Bretyuin]

Hi,

Want to extract data from a table based on certain criteria to create data for a scatter gram chart
Extracted data will be in two columns of data & multiple rows; not just a single cell. I am looking for a way to put extracted data into a range of cells not just a single cell.

Goal:
Look for the desired title (NSS) in column D. For each occurrence of NSS automatically populate columns A and B with however many occurrences of desired title with corresponding yrs. of exp. and salary are found in Data Table. I've manually entered in data into column A and column B to just desired result.

I think there's an easy solution here. I've tried a Vlookup & Index/Match formula but no luck & looked online & on Youtube for a solution. I think an array should work, just not sure how to set up formula. Any help would be greatly appreciated. Thanks!


[TABLE="width: 500"]
<tbody>[TR]
[TD]Col. A[/TD]
[TD]Col. B[/TD]
[TD]Col. C[/TD]
[TD]Col. D[/TD]
[TD]Col. E[/TD]
[TD]Col. F[/TD]
[/TR]
[TR]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD]Salary[/TD]
[/TR]
[TR]
[TD]96500[/TD]
[/TR]
[TR]
[TD]85500[/TD]
[/TR]
[TR]
[TD]96500[/TD]
[/TR]
[TR]
[TD]102500[/TD]
[/TR]
[TR]
[TD]110000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD]Exp.[/TD]
[/TR]
[TR]
[TD]11[/TD]
[/TR]
[TR]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]9[/TD]
[/TR]
[TR]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD]Title[/TD]
[/TR]
[TR]
[TD]NSS[/TD]
[/TR]
[TR]
[TD]NSS[/TD]
[/TR]
[TR]
[TD]NSS[/TD]
[/TR]
[TR]
[TD]NSS[/TD]
[/TR]
[TR]
[TD]NSS[/TD]
[/TR]
[TR]
[TD]ENSS[/TD]
[/TR]
[TR]
[TD]ENSS[/TD]
[/TR]
[TR]
[TD]ENSS[/TD]
[/TR]
[TR]
[TD]SNSS[/TD]
[/TR]
[TR]
[TD]SNSS[/TD]
[/TR]
[TR]
[TD]SNSS[/TD]
[/TR]
[TR]
[TD]SNSS[/TD]
[/TR]
[TR]
[TD]SNSS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD]Exp.[/TD]
[/TR]
[TR]
[TD]11[/TD]
[/TR]
[TR]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]9[/TD]
[/TR]
[TR]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]10[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]10.5[/TD]
[/TR]
[TR]
[TD]9[/TD]
[/TR]
[TR]
[TD]10[/TD]
[/TR]
[TR]
[TD]10[/TD]
[/TR]
[TR]
[TD]7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD]Salary[/TD]
[/TR]
[TR]
[TD]96500[/TD]
[/TR]
[TR]
[TD]85500[/TD]
[/TR]
[TR]
[TD]96500[/TD]
[/TR]
[TR]
[TD]102500[/TD]
[/TR]
[TR]
[TD]110000[/TD]
[/TR]
[TR]
[TD]104000[/TD]
[/TR]
[TR]
[TD]110000[/TD]
[/TR]
[TR]
[TD]110000[/TD]
[/TR]
[TR]
[TD]110000[/TD]
[/TR]
[TR]
[TD]110000[/TD]
[/TR]
[TR]
[TD]100000[/TD]
[/TR]
[TR]
[TD]105000[/TD]
[/TR]
[TR]
[TD]107500[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

A2=IFERROR(INDEX($E:$F,SMALL(IF($D$2:$D$14="NSS",ROW($A$2:$A$14)),ROWS($A$1:A1)),MATCH(A$1,$E$1:$F$1,0)),"")
ARRAY entered, using CTRL SHIFT ENTER, not just enter
then copy across and down

 

[Bretyuin]

Hi,

Thanks for reply and the formula. When I enter the formula provided into cell A2 enter as an array the formula works.
However, after that when I try to copy formula into Column B the formula gives an empty cell. I'm trying to better understand your formula but can't "put all the pieces together".
Any suggestions on what I am doing wrong?