formula to consider a number as text or format when using vlookup or sumproduct

[fayez_MrExcel]

i have a number code (no financial calculation) where the end user either put it as a number or text example 101800, while doing a vlookup, there are number code that is not recognize because of the format, what formula that i can use to consider both format. example : 1008000

 

[Yongle]

Look for both like this
=IFERROR(VLOOKUP(VALUE(Sheet1!A2),Sheet2!B:C,2,0),VLOOKUP(TEXT(Sheet1!A2,"@"),Sheet2!B:C,2,0))

 

[fayez_MrExcel]

Look for both like this
=IFERROR(VLOOKUP(VALUE(Sheet1!A2),Sheet2!B:C,2,0),VLOOKUP(TEXT(Sheet1!A2,"@"),Sheet2!B:C,2,0))

It work in vlookup, how can i apply in sumproduct, see below formula i have have where the ProdCode 1 & 2 i have a doubt entered as as number instead of text

=SUMPRODUCT((ProdCode1=A3)*(ProdCode2=B3)*(Price)). Thanks

 

[Yongle]

Your sumproduct formula would be messy and possibly unreliable

Convert your data and make it consistent
One way:
Use a helper column with this formula to convert current values
EITHER to text with
=TEXT(X2,"@")
OR numbers with
=VALUE(X2)

EITHER use the helper column in VlookUp and SumProduct
OR copy and paste values in helper column over the original values in column X

AND ... prevent future data entry from being inconsistent ?

 

[jasonb75]

It work in vlookup, how can i apply in sumproduct, see below formula i have have where the ProdCode 1 & 2 i have a doubt entered as as number instead of text

=SUMPRODUCT((ProdCode1=A3)*(ProdCode2=B3)*(Price)). Thanks

Is it a format problem or a formula problem? Your formula above will only give results when both product codes match.

As @Yongle has pointed out above, it is always preferable to fix the source data rather than to try and fix the result in the formula, this might work but no guarantees.

=SUMPRODUCT(((ProdCode1+0)=(A3+0))*((ProdCode2+0)=(B3+0))*(Price))

Taking shortcuts may save time now, but will lose more time later.

 

[Dave Patton]

I agree very much with both Jasonb75 and Yongle's statements above.

I provided 2 simple examples on how the SumProduct could work.

T202007a.xlsm
	A	B	C	D	E	F
1		Result			Criteria #	Amount
2	101800	200	200		100	10.00
3	101800	200	200		1000	100.00
4					101800	200.00
5
6
7		Result			Criteria Text	Amount
8	101800	200	200		100	10.00
9	101800	200	200		1000	100.00
10					101800	200.00
11
12
1dd

Cell Formulas
Range	Formula
B9,B2:B3	B2	=IFERROR(VLOOKUP(VALUE(A2),$E$2:$F$10,2,0),VLOOKUP(TEXT(A2,"@"),$E$2:$F$10,2,0))
C2:C3	C2	=SUMPRODUCT(--($E$2:$E$4=A2+0),($F$2:$F$4))
B8	B8	=IFERROR(VLOOKUP(VALUE(A8),$E$8:$F$10,2,0),VLOOKUP(TEXT(A8,"@"),$E$8:$F$10,2,0))
C8:C9	C8	=SUMPRODUCT(--($E$8:$E$10=A8&""),($F$8:$F$10))

=SUMPRODUCT(--($E$2:$E$4=A3),($F$2:$F$4)) works OK with number as expected
=SUMPRODUCT(--($E$2:$E$4=A2+0),($F$2:$F$4)) works OK with A2 Text and A3 as number

=SUMPRODUCT(--($E$8:$E$10=A8),($F$8:$F$10)) works with A2 as Text and Text Criteria as expected
=SUMPRODUCT(--($E$8:$E$10=A8&""),($F$8:$F$10)) works OK if A is Text or Number and Criteria is Text

 

[fayez_MrExcel]

Thanks