Tweaked Vlookup to ignore context?

[mss90]

Hi

I was wondering, is it possible to edit the vlookup formula so that it ignores context and only looks for certain predefined terms from a list. For example: Vlookup "silty clay" in a column where it says "slightly silty clay"?
Or is there a search type function that would retun just specific text (predefined in a list) and ignore the rest of the context?

Thanks

Best regards,

Marius

[jeffreybrown]

Can you post a small sample of what you have and what you expect?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[mss90]

There you go, hope that makes sense.

Thanks for quick reply.

[63falcondude]

If "gravelly silty clay" is found in the text to search, so is "silty clay" and "clay".
Lookup functions such as VLOOKUP and INDEX MATCH return the first match.
This means that whichever match is found first in column A will be the one to be returned.

One way to try and combat this would be to sort column A in a way that has the most specific items up top then use a formula like this:

C2 =INDEX(definedlist,MATCH(TRUE,ISNUMBER(SEARCH(definedlist,B2)),0)) Ctrl Shift Enter

[mss90]

Did that work for you? I am just getting #N/A when trying it?

[63falcondude]

Yes it did. Did you confirm the formula using Ctrl Shift Enter instead of just Enter?

See attachment. I put an empty column in between your lookup column and the text to search for visual clarity.

[FlameRetired]

Another way.

Please Login or Register  to view this content.

[mss90]

If "gravelly silty clay" is found in the text to search, so is "silty clay" and "clay".
Lookup functions such as VLOOKUP and INDEX MATCH return the first match.
This means that whichever match is found first in column A will be the one to be returned.

One way to try and combat this would be to sort column A in a way that has the most specific items up top then use a formula like this:

C2 =INDEX(definedlist,MATCH(TRUE,ISNUMBER(SEARCH(definedlist,B2)),0)) Ctrl Shift Enter

Okay so I works now. I did sort the list with the more specific terms at the top like: silty gravelly clay, silty clay, clay...
However, now is it searches for just clay it returns the first cell with clay in it, which in this case is silty sandy gravelly clay. How can I add on an exact match on it?

Also I noticed when I search for slightly silty clay (it should ignore slightly) it returns silty sandy gravelly organic cobbles?

Cheers