VLOOKUP Question

[davygryan]

I have a sheet that I am trying to use to pull data based on a chemical name.

For this I am using VLOOKUP.
However I am having a problem in that if someone enters the chemical name incorrectly it doesnt pull up any data.

An example is if some one enters "Dioxane" as the chemical the VLOOKUP won't give any data as the exact name for the chemical is "1,4 Dioxane".

Is there any way of getting around this so that if someone doesn't enter the full name it will still return the correct data

 

[JD_GFI]

Hi there,

New to this forum and puzzling my way through so hopefully I have this right

You could try using MATCH and INDEX instead. If I understand correctly then an example might be

=INDEX('Range containing data to return',MATCH( "*"&'cell reference that name is input in','Range containing chemical names to compare against',FALSE),1)

This should return an entry for any chemical name that has other characters preceding it.

 

[John Davis]

I have a sheet that I am trying to use to pull data based on a chemical name.

For this I am using VLOOKUP.
However I am having a problem in that if someone enters the chemical name incorrectly it doesnt pull up any data.

An example is if some one enters "Dioxane" as the chemical the VLOOKUP won't give any data as the exact name for the chemical is "1,4 Dioxane".

Is there any way of getting around this so that if someone doesn't enter the full name it will still return the correct data

You could use a wildcard VLOOKUP. But I think you'd still have a problem if the name was mispelled.

VLOOKUP("*" & LOOKUPVALUE & "*",......

 

[davygryan]

You could use a wildcard VLOOKUP. But I think you'd still have a problem if the name was mispelled.

VLOOKUP("*" & LOOKUPVALUE & "*",......

That works a treat thanks very much.

Next problem I have is if i have more than 1 chemical with the same name in part of the name is there any way of getting the extra values returned because at the moment it will only return the first chemical that has for example "ethanol" in the name and none of the rest of them

 

[John Davis]

That works a treat thanks very much.

Next problem I have is if i have more than 1 chemical with the same name in part of the name is there any way of getting the extra values returned because at the moment it will only return the first chemical that has for example "ethanol" in the name and none of the rest of them

I don't think you can achieve that with VLOOKUP. You may want too try the INDEX MATCH method mentioned in Post #2. Perhaps that will work for you.