LOOKUP where there are approximate matches with the lookup value - how to achieve?

[AliJay64]

I am trying to use XLOOKUP to return the value of Cell J2 into Cell D2 based on the value of B2. This will work for this line as both B2 and I2 are an exact match.

How would I use XLOOKUP for the other cells as the cells in the rest of column B do not match exactly with column I?

Am I going about this the wrong way and should I be using some other formula to achieve the end result?

[AliGW]

1. Your ttitle should tell us what you are trying to do with the formula.
2. Your attachment should show us the results you are looking for.

As it stands, I cannot answer your question because I have no idea wht you think is wrong with what you have done.

[AliJay64]

Hi AliGW,

I have amended the title and uploaded the example again. Hopefully this meets the requirements you mentioned.

[AliGW]

Thanks for the title change, but it's still not telling me what you want to do.

Forget XLOOKUP - just say what the issue is that you are trying to solve. Then answer my questions, please.

EDIT: I've done it for you - please take note for future reference.

[AliGW]

Based on your NEW attachment, I suggest you provide a definitive list of variants - for example, provide a list of the software WITHOUT version numbers and any variations (e.g. where words can be used instead of numbers as in 360). This will need to be elsewhere in the workbook. Once done, post the workbook again.

[AliJay64]

Based on your NEW attachment, I suggest you provide a definitive list of variants - for example, provide a list of the software WITHOUT version numbers and any variations (e.g. where words can be used instead of numbers as in 360). This will need to be elsewhere in the workbook. Once done, post the workbook again.

Thanks for your help on this. Unfortunately I have no option but to use the application names as they are. These are provided to me by another team so cannot amend them.

[JeteMc]

I believe that you may misunderstand what AliGW is asking you to do.
In the attached file:
1. Set up a range of list of software in XFD2:XFD7 based on what I feel AliGW suggests.
2. Use a formula in column L to match the data in column XFD to that in column B (Note that the formula in column may be moved and/or that column hidden for aesthetic purposes).
The formula in column L is: =INDEX(XFD$2:XFD$7,AGGREGATE(15,6,(ROW(XFD$2:XFD$7)-ROW(XFD$1))/(SEARCH(XFD$2:XFD$7,B2)),1))
3. Sort the data in columns I:J alphabetically (based on column I).
The formula that populates column is: =SUMIFS(J$2:J$7,I$2:I$7,L2&"*")
Let us know if you have any questions.

[kvsrinivasamurthy]

Try this
In D2 copied down

Please Login or Register  to view this content.

[AliJay64]

Many thanks to all for helping with this one. It will save a lot of work for me as this report is run monthly.

[AliGW]

Thanks for the feedback.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.