IFS or VLOOKUP #VALUE! Error

[shaw600]

Hi

I keep getting a #VALUE! error from the formula:

=IFS(M10="Low",VLOOKUP(A10,'Low Prevention Database'!A:C,2,0),M10="Medium",VLOOKUP(A10,'Medium Prevention Database'!A:C,2,0),M10="High",VLOOKUP(A10,'High Prevention Database'!A:C,2,0))

It is looking up a cell that contains writing but keeps returning a #VALUE!.

When I reduced the amount of writing in the cell and it worked but I need it to work with all of the writing in the cell.

I hope that makes sense!

Thank you in advance.

Daniel

 

[T. Valko]

I don't have the IFS function in my versions of Excel so I'm taking a guess.

Instead of: M10="Low"

Try replacing that with:

ISNUMBER(SEARCH("Low",M10))

Do the same for the other variables.

 

[Declamatory]

I don't have the IFS function either.

Try:

=IF(M10="Low",VLOOKUP(A10,'Low Prevention Database'!A:C,2,0),IF(M10="Medium",VLOOKUP(A10,'Medium Prevention Database'!A:C,2,0),IF(M10="High",VLOOKUP(A10,'High Prevention Database'!A:C,2,0),"Error. Value not in Database")))