Matt L
Member
Registered: 17th Apr 06
User status: Offline
|
Im not sure if these are possible but
- is there a way to do some form of vlookup that doesnt require the other cell to be identical? so it looks for a cell that contains the word/phrase your looking for?
for example i would want it to look up 'elite' but the cell its looking into may say 'elite car care' but i would want it to find what ever is next to that cell.
also...
- is there a way to make the =left(a2,6) type formula to pick up however many letters before i space?
for example
wh vp10
wh vp8
I would just want the 'vp10' and ''vp8' bit, so i couldnt use 4 as it would give me a space in the one that only has 3 characters.
Cheers.
|
Brett
Premium Member
Registered: 16th Dec 02
Location: Manchester
User status: Offline
|
Probably something like *elite* for the first one?
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
You can substring the longer cell but it depends how you're cutting it down.
If you wanted the first 5 chars, use LEFT()
First word you can interpolate a FIND() function and go left of the space.
Second bit of the question, FIND() will do it. It returns an integer of the position of the specific search text.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
In fact SEARCH() might be better as its not case sensitive.
|
Matt L
Member
Registered: 17th Apr 06
User status: Offline
|
I dont think the seach/find ones will work... or i am not doing them properly.
if anyone can type a quick example to get this to work for me that would be great.
cell
A1 = elite car care
A2 = 7
C1 = elite
C2 - i want to show the value in A2 by looking up C1 within A1
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
Put the substring stuff in a cell first and see what that gives you, then you can see if you have a match.
|
Gaz
Member
Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline
|
I might have a formula on a spreadsheet in work that will effectively do what you are asking. I'll try and remember to write it down for you.
|
Gaz
Member
Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline
|
It might be best (if it's not to work specific) to host the document online and then we can take a look and try and fix it up for you 
|
Chris
Premium Member
Registered: 21st Sep 99
User status: Offline
|
You want INSTR but would require VBA function.
Basically will give value where the match string ocurs in this case 1
so
elite ; INSTR(A1,C1)+LEN(C1)+2= 7
car ; INSTR(A1,C1)+LEN(C1)+2 = 11
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
quote:
Originally posted by Matt L
I dont think the seach/find ones will work... or i am not doing them properly.
if anyone can type a quick example to get this to work for me that would be great.
cell
A1 = elite car care
A2 = 7
C1 = elite
C2 - i want to show the value in A2 by looking up C1 within A1
I'm assuming the value you want to SUM is always a number(A2 in the example). In which case it's much more simple than above:
=SUMIF(A1,"*elite*",A2)
Where
A1 = the value you're looking for 'elite' in
A2 = the value you want to return
Note, it would be better if possible to have the value you want to return in B1 next to the lookup value and the formula in C1. Then you can drag it down.
[Edited on 18-01-2013 by A2H GO]
|
Matt L
Member
Registered: 17th Apr 06
User status: Offline
|
that one wont work as it requires me typing the word elite, the spreadsheet i want to use it on will have shed load of suppliers names so would be pointless to set it up on every row.
|
Ste
Premium Member
Registered: 5th Mar 03
Location: Taif, Saudi Arabia
User status: Offline
|
Need to see data to have any idea to help! Upload so that we can have a play!
I would rather lose by a mile because i built my own car, than win by an inch because someone else built it for me.
|
Matt L
Member
Registered: 17th Apr 06
User status: Offline
|
where can I upload an excel file?
ill have to knock one together tonight as its work related so will have to make one up.
|
Ste
Premium Member
Registered: 5th Mar 03
Location: Taif, Saudi Arabia
User status: Offline
|
loads of free cloud based sites out there. google drive is what i use.
I would rather lose by a mile because i built my own car, than win by an inch because someone else built it for me.
|
