VLookup & Consolidate formula in one

[BKGLTS]

Hello,

I am looking for a couple formulas to solve this issue, but one would be even better. I took a two formula approach in the example below. 1st formula will find rows in column A that begin with a defined letter, such as "M" in the example below, and then return a value in a different column from that same row. Basically a vlookup, but specifying the starting letter of the lookup value instead of the entire cell value. This done in two columns next to each other (Step 1 - Columns F&G) and then I need a 2nd formula that takes columns F&G and consolidates them into two other columns (Step 2-Columns J&K). Columns A,B,C is the current state.

I have:

[TABLE="width: 768"]
<colgroup><col span="12"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="colspan: 2"]CURRENT STATE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]STEP 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]STEP 2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]M1[/TD]
[TD]2[/TD]
[TD]Item1[/TD]
[TD] [/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Item1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10[/TD]
[TD]Item1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A1[/TD]
[TD]6[/TD]
[TD]Item2[/TD]
[TD] [/TD]
[TD][/TD]
[TD]8[/TD]
[TD]Item1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD]Item6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Item6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3[/TD]
[TD]Item8[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]M2[/TD]
[TD]8[/TD]
[TD]Item1[/TD]
[TD] [/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Item8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]C5[/TD]
[TD]9[/TD]
[TD]Item5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]M3[/TD]
[TD]6[/TD]
[TD]Item6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]M5[/TD]
[TD]3[/TD]
[TD]Item8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]G2[/TD]
[TD]5[/TD]
[TD]Item9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]A2[/TD]
[TD]7[/TD]
[TD]Item10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]



Thank you!
B

 

[BKGLTS]

I think changing the formula in F1 to below should work:

=IFERROR(INDEX(WhiteSheets!$F$1:$F$10402,SMALL(IF(MATCH(LEFT(WhiteSheets!$A$1:$A$10402)&WhiteSheets!$F$1:$F$10402,LEFT(WhiteSheets!$A$1:$A$10402)&WhiteSheets!$F$1:$F$10402,0)*(LEFT(WhiteSheets!$A$1:$A$10402)=$E$1)*(WhiteSheets!$F$1:$F$10402<>"")=(ROW(WhiteSheets!$A$1:$A$10402)-ROW(WhiteSheets!$A$1)+1),ROW(WhiteSheets!$A$1:$A$10402)-ROW(WhiteSheets!$A$1)+1),ROWS(F$1:F1))),"")

Please confirm with CSE

Thank you!
Thank you!
Thank you!

Perfecto!

 

[Aryatect]

Glad could help