Help with Index function to find 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.

See attached Excel file with the data, would like formula to place result in yellow highlighted area.

Goal:
Look for the desired title (NSS) in column E. 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 Columns F and G. I've manually entered in data into column A and column B (yellow highlighted area) to show 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!

[shukla.ankur281190]

try

A19=INDEX($F$19:$G$31,SMALL(IF($E$19:$E$31=TRIM(RIGHT(SUBSTITUTE($A$17,":",REPT(" ",50)),50)),ROW($E$19:$E$31)-ROW($E$19)+1),ROWS(C$19:C19)),MATCH(A$18,$F$18:$G$18,0)) with CRTL+SHIFT+ENTER and drag down.

Ps.Cell F18 need to change with Exp.

[Bretyuin]

Hi,

Thanks for reply and formula. It provides the correct results for Column A but I also want the formula to derive the corresponding years of experience for Col. B. When I use your formula and try to copy or drag it from Column A into column B I get "#N/A". Any suggestions on how to get formula to derive the correct salary in column A and corresponding years of experience in Col. B

[udprocks]

Hi,

Thanks for reply and formula. It provides the correct results for Column A but I also want the formula to derive the corresponding years of experience for Col. B. When I use your formula and try to copy or drag it from Column A into column B I get "#N/A". Any suggestions on how to get formula to derive the correct salary in column A and corresponding years of experience in Col. B

Try,

in B19

=INDEX(F19:F31,MATCH(A19,G19:G31,0))

[shukla.ankur281190]

Hi,

Thanks for reply and formula. It provides the correct results for Column A but I also want the formula to derive the corresponding years of experience for Col. B. When I use your formula and try to copy or drag it from Column A into column B I get "#N/A". Any suggestions on how to get formula to derive the correct salary in column A and corresponding years of experience in Col. B

As I said F18 cell change value with Exp. only and the results will come automatically

[Bretyuin]

Hi,

Thanks for reply. That works but is there just a single formula that can calculate values for Column A and Column B instead of two formulas?

[udprocks]

Hi,

Thanks for reply. That works but is there just a single formula that can calculate values for Column A and Column B instead of two formulas?

just see post no.#5, from mr. ankur agarwal.

[Bretyuin]

Hi,

Thanks for reply. That works but is there just a single formula that can calculate values for Column A and Column B instead of two formulas?

[udprocks]

Hi,

Thanks for reply. That works but is there just a single formula that can calculate values for Column A and Column B instead of two formulas?

see post#5,

u need to change the title in F18, "Yrs. Of" to "Exp."

and it will work.

[Bretyuin]

Thanks! That solves it! I appreciate the help

[udprocks]

Thanks! That solves it! I appreciate the help

Happy to help ...

and also say thanks to Mr. ankur shukla, and please mark thread as solved.

[Bretyuin]

Thanks, that worked. I appreciate the help.