SUMIF returning numbers rather than text

[PSI94]

Good evening all,

As the post title suggests, my SUMIF formulas are returning numbers instead of text. I've read various posts on this and understand that a SUMIF won't return text, however whenever I try to re-write the formula as a V-LOOKUP I am failing miserably My current SUMIF formula is -

=SUMIFS('Jan 2022 Test Cases'!K:K,'Jan 2022 Test Cases'!$A:$A,'Jan 2022 Compare'!$D3)

Has anyone any idea on how I could ammend this to return the relevant text? This is the only column that I am wanting the text to display from, with the SUMIF correctly returning the relevant numbers in the other columns. Will the amendment return the text that correlates with my current returned figures?

Thank you very much in advance

 

[jtakw]

Hi,

Try the VLOOKUP like this, Untested:

=VLOOKUP('Jan 2022 Compare'!$D3,'Jan 2022 Test Cases'!$A:$K,11,0)

 

[StephenCrump]

Welcome to the Forum!

This will return the first value: =INDEX('Jan 2022 Test Cases'!K:K,MATCH('Jan 2022 Compare'!$D3,'Jan 2022 Test Cases'!$A:$A,))

Are you expecting more than one value, which is what your use of SUMIF implies?

 

[PSI94]

Hi,

Try the VLOOKUP like this, Untested:

=VLOOKUP('Jan 2022 Compare'!$D3,'Jan 2022 Test Cases'!$A:$K,11,0)

That didn't work but appreciate your help - thanks so much!

 

[PSI94]

Welcome to the Forum!

This will return the first value: =INDEX('Jan 2022 Test Cases'!K:K,MATCH('Jan 2022 Compare'!$D3,'Jan 2022 Test Cases'!$A:$A,))

Are you expecting more than one value, which is what your use of SUMIF implies?

Thank you! That worked a treat!

Yeah, so I've a data dump on one tab that I've created a unique key for each line - approx 100. Then on another tab I've a list of keys - approx 10 which I'm wanting the formula to find the corresponding key from the other tab and show the relevant information. It's primarily numbers so the SUMIF was fine for that, but it was one column which has text that was causing the headache.

Sorry if that doesn't make sense!

 

[StephenCrump]

=VLOOKUP('Jan 2022 Compare'!$D3,'Jan 2022 Test Cases'!$A:$K,11,0)

That didn't work but appreciate your help - thanks so much!

Are you sure? ... it works fine for me.

 

[StephenCrump]

Sorry if that doesn't make sense!

It makes sense. If you're certain that the SUMIF() will only find one match, the result will be the same as a VLOOKUP or INDEX/MATCH approach.