VLOOKUP returns text instead of number

[mib1019]

I'm sure I'll get an answer here.

I have a lookup table with 4 columns. Here is a screenshot:

The left column is a helper column to get VLookup to use two criteria.

My VLookup issue is in the Req'd Hrs on the data table. The formula is: =IF(ISNA(VLOOKUP([@Level]&"|"&[@Activity],Table2,4,FALSE)),0,VLOOKUP([@Level]&"|"&[@Activity],Table2,4,FALSE))

Running a pivot table on this data is returning the Req'd Hrs as text instead of a number. Doesn't make sense as the Remaining Hours column is seeing the Req'd Hrs as a number to get the result.

Hope you can help me! Thanks in advance.
MIB

 

[Joe4]

I see that your last two entries do not appear to have anything entered into that Req'd Hrs column.
Is it really blank, or is there a space in there?

Regardless, you should be able to convert your values returning numbers as text to numbers by doing a simple mathematical activity like adding 0 to it, i.e.
=IF(ISNA(VLOOKUP([@Level]&"|"&[@Activity],Table2,4,FALSE)),0,VLOOKUP([@Level]&"|"&[@Activity],Table2,4,FALSE))+0

Also, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’). If you are using a newer version, you can use the new "IFERROR" function instead of "IF(ISNA(...".

 

[mib1019]

Thanks for that suggestion on my account details. Update. Running subscription Office 365, so the most recent version. I'll move away from the IsNA function.

The last two entries in the lookup table are blank, not spaces. There are no records, so far, in the time input table, that are looking up those blank values.

BUT...I added the '+0' to the lookup formula, and the pivot table is still seeing the Req'd Hrs value as text.

Any ideas?

Thanks for your help!

 

[Joe4]

BUT...I added the '+0' to the lookup formula, and the pivot table is still seeing the Req'd Hrs value as text.

How exactly are you determining that the value being returned is Text and not Numeric?

Are you trying to use this number in another mathematical calculations elsewhere (and is that why it is an issue)?
If so, what is the formula that references it?

 

[mib1019]

Thanks for the help. It was happening on a pivot table. I deleted it and recreated; now it is working fine.

 

[Joe4]

OK, glad you got it sorted.

 

[Peter_SSs]

Not sure if it turned out you needed that +0 on the end but that formula can be written much more simply as

=IFNA(VLOOKUP([@Level]&"|"&[@Activity],Table2,4,FALSE),0)+0

Or an alternative formula that does not require the helper column at all in Table2, or an error check:

=SUMIFS(Table2[Req''d Hrs],Table2[Level],[@Level],Table2[Activity],[@Activity])

A further advantage of this last formula is that if subsequently columns are added/removed in Table2, the formula will not need editing as the VLOOKUP formula would (most likely).

 

[mib1019]

Not sure if it turned out you needed that +0 on the end but that formula can be written much more simply as

=IFNA(VLOOKUP([@Level]&"|"&[@Activity],Table2,4,FALSE),0)+0

Or an alternative formula that does not require the helper column at all in Table2, or an error check:

=SUMIFS(Table2[Req''d Hrs],Table2[Level],[@Level],Table2[Activity],[@Activity])

A further advantage of this last formula is that if subsequently columns are added/removed in Table2, the formula will not need editing as the VLOOKUP formula would (most likely).

Thanks everyone. I've got it solved.