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

 

[Aryatect]

Hi, Try below:

Book1
	A	B	C	D	E	F	G
1	M1	2	Item1		M	Item1	10
2	A1	6	Item2			Item6	6
3						Item8	3
4	M2	8	Item1
5	C5	9	Item5
6	M3	6	Item6
7
8	M5	3	Item8
9	G2	5	Item9
10	A2	7	Item10
Sheet3

Cell Formulas
Range	Formula
G1	=IF(F1<>"",SUMPRODUCT(($E$1&F1=LEFT($A$1:$A$10)&$C$1:$C$10)*$B$1:$B$10),"")
F1	{=IFERROR(INDEX($C$1:$C$10,SMALL(IF(MATCH(LEFT($A$1:$A$10)&$C$1:$C$10,LEFT($A$1:$A$10)&$C$1:$C$10,0)*(LEFT($A$1:$A$10)=$E$1)*($A$1:$A$10<>"")=(ROW($A$1:$A$10)-ROW($A$1)+1),ROW($A$1:$A$10)-ROW($A$1)+1),ROWS(F$1:F1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[BKGLTS]

Hi, Try below:

	A	B	C	D	E	F	G
M1	Item1	M	Item1
A1	Item2	Item6
Item8
M2	Item1
C5	Item5
M3	Item6
M5	Item8
G2	Item9
A2	Item10

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]G1[/TH]
[TD="align: left"]=IF(F1<>"",SUMPRODUCT(($E$1&F1=LEFT($A$1:$A$10)&$C$1:$C$10)*$B$1:$B$10),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]F1[/TH]
[TD="align: left"]{=IFERROR(INDEX($C$1:$C$10,SMALL(IF(MATCH(LEFT($A$1:$A$10)&$C$1:$C$10,LEFT($A$1:$A$10)&$C$1:$C$10,0)*(LEFT($A$1:$A$10)=$E$1)*($A$1:$A$10<>"")=(ROW($A$1:$A$10)-ROW($A$1)+1),ROW($A$1:$A$10)-ROW($A$1)+1),ROWS(F$1:F1))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

So columns A, B, C are on a different sheet than E,F,G.
I didn't mention this because it is usually pretty simple to modify for this.
I got your F1 Array Formula to work in this configuration, but the G1 formula I can't get it work.
Here is how I modified it.
It just returns a #VALUE ! error.
Any ideas please?

=IF(F1<>"",SUMPRODUCT(($E$1&F1=LEFT(WhiteSheets!$A$1:$A$10402)&WhiteSheets!$F$1:$F$10402)*WhiteSheets!$B$1:$B$10402),"")



Also, here is what I did to the F1 formula that is working:

{=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!$A$1:$A$10402<>"")=(ROW(WhiteSheets!$A$1:$A$10402)-ROW(WhiteSheets!$A$1)+1),ROW(WhiteSheets!$A$1:$A$10402)-ROW(WhiteSheets!$A$1)+1),ROWS(WhiteSheets!F$1:F1))),"")}



Thank you!
B

 

[Aryatect]

Glad to help and thanks for the feedback!

 

[BKGLTS]

Glad to help and thanks for the feedback!

Any idea on how to get the G1 formula to work?

 

[Aryatect]

=IF(F1<>"",SUMPRODUCT(($E$1&F1=LEFT(WhiteSheets!$A$1:$A$10402)&WhiteSheets!$F$1:$F$10402)*WhiteSheets!$E$1:$E$10402),"")

 

[BKGLTS]

=IF(F1<>"",SUMPRODUCT(($E$1&F1=LEFT(WhiteSheets!$A$1:$A$10402)&WhiteSheets!$F$1:$F$10402)*WhiteSheets!$E$1:$E$10402),"")

Didn't work, not sure where you are going with the Column E on WhiteSheets, nothing of consequence is in that column.

On the sheet named "WhiteSheets":
Column A is where the M1, M2, M3, etc. values are.
Column F is where the Item1, Item2, Item3,etc. values are.
Column B is where the 2,6,8,etc. values are.

On the current sheet where the formulas are:
Column E is where the "M" is.
Column F is where the F1 formula is.
Column G is where the G1 formula is.

Your formulas work fine with everything on the same sheet.

Thank you!
B

 

[Aryatect]

I thought your numbers might be in column but now it clears up. do you have any text in column B of WhiteSheets anywhere in the range? the moment I insert any text there, the formula fails.

If there is text use below array formula:

=IF(F1<>"",SUM(($E$1&F1=LEFT(WhiteSheets!$A$1:$A$10402)&WhiteSheets!$F$1:$F$10402)*IF(ISNUMBER(WhiteSheets!$B$1:$B$10402),WhiteSheets!$B$1:$B$10402,0)),"")

Confirm with Ctrl+Shift+Enter not just enter

 

[BKGLTS]

I thought your numbers might be in column but now it clears up. do you have any text in column B of WhiteSheets anywhere in the range? the moment I insert any text there, the formula fails.

If there is text use below array formula:

=IF(F1<>"",SUM(($E$1&F1=LEFT(WhiteSheets!$A$1:$A$10402)&WhiteSheets!$F$1:$F$10402)*IF(ISNUMBER(WhiteSheets!$B$1:$B$10402),WhiteSheets!$B$1:$B$10402,0)),"")

Confirm with Ctrl+Shift+Enter not just enter

Yes indeed. There is text. That fixed the issue, it is working now!
Thank you so much for thinking of that!

One other thing I notice is there are Rows that have an M in column A but nothing in Column F.
So the formulas are reporting 0 for those.
Do you have a way to tell it if Column F is (blank) to ignore it so I don't get 0's in the list please?
Column F has drop down lists by the way if that effects anything.

 

[Aryatect]

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