Lookup on multi value cell in Excel
Discussion
Hi all, would appreciate some thoughts on this.
I have an Excel spreadsheet and I need to lookup a value cells with multi comma separated values.
Note that some of the values are over 256 characters.
I've tried Vlookup but it only works for cells where the length is less that 256 characters.
Below is sample data. Say I want to lookup value "999"
My VLOOKUP formula is :
and it's expected to return "DEF".
This doesn't work if values in A is more than 256 characters. I've had a look at suggestions using INDEX and MATCH but that doesn't seem to work.
I have an Excel spreadsheet and I need to lookup a value cells with multi comma separated values.
Note that some of the values are over 256 characters.
I've tried Vlookup but it only works for cells where the length is less that 256 characters.
Below is sample data. Say I want to lookup value "999"
My VLOOKUP formula is :
=VLOOKUP("*"& 999 & "*", A1:B2,2,false) and it's expected to return "DEF".
This doesn't work if values in A is more than 256 characters. I've had a look at suggestions using INDEX and MATCH but that doesn't seem to work.
| A | B |
|---|---|
| 123,3434,52525 | ABC |
| 99494,9349,98983,999 | DEF |
anxious_ant said:
Hi all, would appreciate some thoughts on this.
I have an Excel spreadsheet and I need to lookup a value cells with multi comma separated values.
Note that some of the values are over 256 characters.
I've tried Vlookup but it only works for cells where the length is less that 256 characters.
Below is sample data. Say I want to lookup value "999"
My VLOOKUP formula is :
and it's expected to return "DEF".
This doesn't work if values in A is more than 256 characters. I've had a look at suggestions using INDEX and MATCH but that doesn't seem to work.
Could you utilise the find formula? So put this in column C against each recordI have an Excel spreadsheet and I need to lookup a value cells with multi comma separated values.
Note that some of the values are over 256 characters.
I've tried Vlookup but it only works for cells where the length is less that 256 characters.
Below is sample data. Say I want to lookup value "999"
My VLOOKUP formula is :
=VLOOKUP("*"& 999 & "*", A1:B2,2,false) and it's expected to return "DEF".
This doesn't work if values in A is more than 256 characters. I've had a look at suggestions using INDEX and MATCH but that doesn't seem to work.
| A | B |
|---|---|
| 123,3434,52525 | ABC |
| 99494,9349,98983,999 | DEF |
=IF(IFERROR(FIND("999",A2,1),"No Match")="No Match", "No Match",B2)
| A | B | C |
|---|---|---|
| 123,3434,52525 | ABC | No Match |
| 99494,9349,98983,999 | DEF | DEF |
As far as I know the max length is 255 for Find/Match/Lookup.
My simple solution (which may not work for you) was to create another column to trim the data to 200 characters, as I worked out that was how many I'd need to result in unique strings...but I only had 17,000 entries to go through.
My simple solution (which may not work for you) was to create another column to trim the data to 200 characters, as I worked out that was how many I'd need to result in unique strings...but I only had 17,000 entries to go through.
I'm not totally sure how to paste tables/ code etc correctly in this forum, but the way i'd achieve what you're trying to do is:
have a value to find cell. in my case it's cell C1, and in that cell is 494
Then, enter a blank column to the left of your data. in my case it's column A
In column A put this formula in:
=IFERROR(IF(SEARCH($C$1,B4)>=1,$C$1,0),0) --> i.e. seach each cell for the number you've put in cell C1... once you've found that number, return it.
Then, it's a simple vlookup. In my case this is in cell D1
=VLOOKUP(C1,$A$3:$C$5,3,0)
Not sure if this achieves what you are after.
have a value to find cell. in my case it's cell C1, and in that cell is 494
Then, enter a blank column to the left of your data. in my case it's column A
In column A put this formula in:
=IFERROR(IF(SEARCH($C$1,B4)>=1,$C$1,0),0) --> i.e. seach each cell for the number you've put in cell C1... once you've found that number, return it.
Then, it's a simple vlookup. In my case this is in cell D1
=VLOOKUP(C1,$A$3:$C$5,3,0)
Not sure if this achieves what you are after.
Luke-36 said:
I'm not totally sure how to paste tables/ code etc correctly in this forum, but the way i'd achieve what you're trying to do is:
have a value to find cell. in my case it's cell C1, and in that cell is 494
Then, enter a blank column to the left of your data. in my case it's column A
In column A put this formula in:
=IFERROR(IF(SEARCH($C$1,B4)>=1,$C$1,0),0) --> i.e. seach each cell for the number you've put in cell C1... once you've found that number, return it.
Then, it's a simple vlookup. In my case this is in cell D1
=VLOOKUP(C1,$A$3:$C$5,3,0)
Not sure if this achieves what you are after.
Interesting, I will have a look at the SEARCH function. Unfortunately it's not just a single cell that will be used for the search, I have another list of values to search through.have a value to find cell. in my case it's cell C1, and in that cell is 494
Then, enter a blank column to the left of your data. in my case it's column A
In column A put this formula in:
=IFERROR(IF(SEARCH($C$1,B4)>=1,$C$1,0),0) --> i.e. seach each cell for the number you've put in cell C1... once you've found that number, return it.
Then, it's a simple vlookup. In my case this is in cell D1
=VLOOKUP(C1,$A$3:$C$5,3,0)
Not sure if this achieves what you are after.
anxious_ant said:
Wow, many helpful replies here thank you all! Sorry I don't think I've mastered the art of multi quoting yet...
I will have a look at the suggestions and see where I get to. Might have to import data into SQL for manipulation. Shame about the 256 character limit in Excel.
Generally if you need to use a VLOOKUP I would drop the tables into PowerPivot, setup a relationship between the two tables and sack off formulas completely. I will have a look at the suggestions and see where I get to. Might have to import data into SQL for manipulation. Shame about the 256 character limit in Excel.
If you're talking about using SQL to manipulate the data to get this to work, load it into PowerQuery to do that bit, then load the result into PowerPivot, setup a relationship and get your output into a Pivot Table.
The 256 character limit is a Cell limit, not an Excel limit, PowerQuery will deal with it fine as it's not loading into a Cell. I think anyway, not sure if I've ever had to test that.
paulrockliffe said:
anxious_ant said:
Wow, many helpful replies here thank you all! Sorry I don't think I've mastered the art of multi quoting yet...
I will have a look at the suggestions and see where I get to. Might have to import data into SQL for manipulation. Shame about the 256 character limit in Excel.
Generally if you need to use a VLOOKUP I would drop the tables into PowerPivot, setup a relationship between the two tables and sack off formulas completely. I will have a look at the suggestions and see where I get to. Might have to import data into SQL for manipulation. Shame about the 256 character limit in Excel.
If you're talking about using SQL to manipulate the data to get this to work, load it into PowerQuery to do that bit, then load the result into PowerPivot, setup a relationship and get your output into a Pivot Table.
The 256 character limit is a Cell limit, not an Excel limit, PowerQuery will deal with it fine as it's not loading into a Cell. I think anyway, not sure if I've ever had to test that.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff