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:

	CURRENT STATE				STEP 1				STEP 2
	A	B	C	D	E	F	G	H	I	J	K
1	M1	2	Item1			2	Item1			10	Item1
2	A1	6	Item2			8	Item1			6	Item6
3						6	Item6			3	Item8
4	M2	8	Item1			3	Item8
5	C5	9	Item5
6	M3	6	Item6
7
8	M5	3	Item8
9	G2	5	Item9
10	A2	7	Item10

<colgroup><col span="12"></colgroup><tbody>
</tbody>

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
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

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula G1 =IF(F1<>"",SUMPRODUCT(($E$1&F1=LEFT($A$1:$A$10)&$C$1:$C$10)*$B$1:$B$10),"") <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula 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))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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