Hello,
Wonder if someone could help solve the lookup issue i'm having with Excel 2003.
S2 should = (nothing)
S3 should = |Female
S4 should = (nothing)
Spreadsheet at the moment is not populating S2 correctly from the lookup data.
Hopefully it's something simple
Thanks.
Last edited by Greenal; 09-15-2011 at 08:26 AM. Reason: Change 'S2 should = Male|' to 'S2 should = (nothing)'
Update:
I deleted the about 40 characters from F2, and then the lookup worked.
Q. Does Excel not look at cells if they are in excess of a certain number of characters?
Another update:
Whilst searching for a possible answer it seems Excel (2003?) has a character limit on the CHOOSE part of the formula.
Is there a workaround formula for this?
Many thanks.
It's not exactly clear to me what you are doing here.. in the first post you mention S1, S2 and S3. In the second post you talk about F2...
Can you walk through the problem and exactly where and what the expected results should be and why?
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Originally Posted by NBVC
Hi NVBC,
Yep sure i can no problem...
- The cells i've referred to so far are in the 'AWorkbookin' tab
- The lookup looks at A to O. There is text in E, F and L all in the 'AWorkbookin' tab
- In F2 the term 'repeater rifle' is present which is one of the terms in the lookup table (found in 'Data' tab) and then the formula says, if this term is found add 'Male' to 'C2' on the 'Data' tab.
- Then S2 on the 'AWorkbookin' tab has a formula to pull in data from the 'Data' tab
Hope this helps.
I am quite confused.
The terms, "gunslinger" and "too much" appear in all 3 rows of the AWorkbookin sheet... so should you have Female 3 times in the Data sheet?
Hi NBVs,
Sorry for the confusion :/
Yes you are correct.
If you delete text in F2 of Aworkbookin to less than 255 (leaving in any terms that match the lookup) the lookup for that row will then work...
Okay,
Try this formula in C2 of Data sheet:
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(A$2:A$4,AWorkbookin!$A2:$O2)))),B2,"")
copied down
then copy C2:C4 and paste to F2.
Then formula in S2 of other sheet: =IF(AND(Data!C2="Male",Data!F2="Female"),"",Data!C2&"|"&Data!F2) should work.
Hi again NBVC,
Thankyou very much for that formula - i have tested it out and it works except it seems for one thing:
1/ In the 'Data' sheet column A has the list of words and wherever in the list a word is found (or if multiple words matched the last one is 'used') then the value in column B next to the matched word should be used.
I've uploaded Example2 workbook to illustrate.
In the Example2 workbook on the AWorkbookin sheet:
S3 should = Male| (because ccc is found in F3)
S4 should = x| (because dog is found in F4)
Thank you in advance if you can help with the formula revision.
The formula you have currently in column S, doesn't even attempt to look at column F of AllWorkbookin... how was one supposed to know what you really wanted?
Please explain what exactly the formula in column S is supposed to do.. from scratch.
Column S of 'AWorkbookin' is working fine, as far as i can see.
(Column S, as per the formula you wrote pulls in the value(s), if present in column C and F of 'Data' sheet.)
The issue is on the Data sheet.
For instance C2 should not just equal what B2 is...it should find the last correct match in A2:A4 and show the value from the B column that is on the same row as the last match.
So if 'dog' (A column) is found then x (B column) should populate all corresponding rows in column C which mirror where dog appears in A:O on the AWorkbookin sheet.
Hope this helpsand thanks again.
Are there going to be more that 3 options columns A and D in Data sheet in the real scenario? If so what is max?
Yes.
Columns:
Where C figure is used for the S column at the 'AWorkbookin' D is used as the S column on a separate workbook. (With the possibility of more workbooks being added).
Rows:
There are currently just over 90 (A2:A91) terms. Out of those 90 terms in B2:B91, i input either 'Males' or 'x'. Difficult to say a max, because it is constantly being updated.
(Then i have the same for 'Female).
So, you are correct the MATCH() doesn't work since some cells have more than 255 characters.. but if MATCH() did work, it simply tells you the cell that has the match with A2:O2, If more than one word from Data sheet matched the same cell in A2:O2, then you wouldn't know which was the last word to match within that cell.... so that is not what you want, correct?
If not, then SEARCH() is better, because it finds the exact position in the cell that each word is found... so we can look for MAX search results... the only thing there is, that SEARCH looks for each of the words in the Data sheet in every cell of the A2:O2 range, yielding an array of the number of cells in Data sheet * the number of cells in A2:O2... so I am having difficulty trying to split it out correctly so that you can line up those results to find last cell in Data sheet to match.
I will have to think it through.. maybe ultimately it might need VBA... not sure at the moment....
The hierarchy i need is from the list of terms from the Data sheet (so, A2:A4).
For example: If the term in 'Data' A3 is found anywhere in, say 'AWorkbooin' A2:O2 then the the term from 'Data' B4 is used to populate row in column C where that term is present in the corresponding row on the AWorkbooin sheet .....unless the term in 'Data' A4 is present anywhere in say 'AWorkbooin' A2:O2
To summerise that:
The hierarchy is the list of terms in 'Data' column A. The position in the cell (on AWorkbooin A:O) where the term appears isn't of interest.
Hope that answers you.
Really appreciate your persistence
That is pretty much what I said... you just confirmed it....
I've had a test with my data and all is working great...except one thing:
If there is a match it doesn't seem to be using the list in the data sheet as its hierarchy.
So if both hello and goodbye were featured somewhere in A2:O2 and this is what we had in the list
A B
hello Male
goodbye x
it should look down the column and see hello but then it sees goodbye so regardless of the positioning of those words in A2:AO because goodbye is after / below hello in the list goodbye overrides...
I think this is where we got our wires crossed from earlier posts.
Last edited by Greenal; 09-09-2011 at 09:07 AM.
Try this stab in the dark:
In C2 of Data sheet:
confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.
=LOOKUP(9.9999E+307,SEARCH(A$2:A$4,INDEX(AWorkbookin!$A2:$O2,,MAX(IF(ISNUMBER(SEARCH(A$2:A$4,AWorkbookin!$A2:$O2)),COLUMN(AWorkbookin!$A2:$O2))))),B$2:B$4)
Then copy C2:C4 to F2
Okay, probably that one above is not going to work...
I think this might... but it requires you insert helper columns, since the formulas would have too many nested function for XL2003...
So see attached.
I inserted 2 columns in Data sheet after B and 2 columns after the new column G.
Formulas are in C2:
this extracts the string in the cell that contains last occurance of a matched wordl=INDEX(AWorkbookin!$A2:$O2,,MAX(IF(ISNUMBER(SEARCH(A$2:A$4,AWorkbookin!$A2:$O2)),COLUMN(AWorkbookin!$A2:$O2))))
in D2:
this finds max (or nearest to the end) position within that cell of last occurring word=MAX(IF(ISNUMBER(SEARCH(A$2:A$4,C2)),SEARCH(A$2:A$4,C2)))
in E2:
the final matched text for from Column B to coincide with last matched word.=INDEX(B$2:B$4,MATCH(D2,SEARCH(A$2:A$4,INDEX(AWorkbookin!$A2:$O2,,MAX(IF(ISNUMBER(SEARCH(A$2:A$4,AWorkbookin!$A2:$O2)),COLUMN(AWorkbookin!$A2:$O2))))),0))
Each of these formulas are array formulas, needing CTRL+SHIFT+ENTER confirmation, then each copied down.
Then you can copy C2:E4 to H2 for the Female side.
Those helper columns can be hidden...
You may need to adjust the formulas in column S of the other sheet to coincide with new column positions...
Hope that does it.
I think you might have just sussed it :DTry this stab in the dark:
In C2 of Data sheet:
confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.=LOOKUP(9.9999E+307,SEARCH(A$2:A$4,INDEX(AWorkbookin!$A2:$O2,,MAX(IF(ISNUMBER(SEARCH(A$2:A$4,AWorkbookin!$A2:$O2)),COLUMN(AWorkbookin!$A2:$O2))))),B$2:B$4)
Then copy C2:C4 to F2
Only thing is i'm getting the #N/A where the term in Data A (Data F) isn't found anyway in WWorkbookin A:O.
---------------
(Have just seen post #19 and #20)
----------------
Last edited by Greenal; 09-08-2011 at 12:18 PM.
Actually one more correction/change...
in E2, we can actually just use this formula:
CSE confirmed=INDEX(B$2:B$4,MATCH(D2,SEARCH(A$2:A$4,C2),0))
since in C2, we already pulled the cell string...
I've entered some values and i think it's just about thereActually one more correction/change...
in E2, we can actually just use this formula:
CSE confirmed=INDEX(B$2:B$4,MATCH(D2,SEARCH(A$2:A$4,C2),0))
since in C2, we already pulled the cell string...
Last couple of issues:
1/ When there is a match for male but not for female (or vice versa) it shows #N/A in S on AWorkbookin... can it show either Male or Female in S?
2/ When there isn't a match for male or female it shows #N/A in S on AWorkbookin... can it leave S blank?
The last solution will give you a 0 instead of #N/A if not found to hide those, format those cells as custom: 0;-0;;@
So are we now talking about fixing the formulas in column S of the AllWorkbookin sheet? Or is it back to the Data sheet formulas?
I think either way should be just fine
Does changing the E2 formula in Data sheet to:
confirmed with CTRL+SHIFT+ENTER and copied down, then copied to the Female side at J2, work?=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(B$2:B$4,MATCH(D2,SEARCH(A$2:A$4,C2),0))))
Yes it looks like it does :DDoes changing the E2 formula in Data sheet to:
confirmed with CTRL+SHIFT+ENTER and copied down, then copied to the Female side at J2, work?=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(B$2:B$4,MATCH(D2,SEARCH(A$2:A$4,C2),0))))
Going to have a further play with it etc and add the formula to my real data to double check all is well, but it's looking good
Thank you very, very much for all your help. (I will be donating to your charity, just so you know).
Many thanks.
Great, you're welcome, now let's keep the fingers crossed.. That was a real brain - teaserYes it looks like it does :D
Going to have a further play with it etc and add the formula to my real data to double check all is well, but it's looking good
Thank you very, very much for all your help.
Thank you(I will be donating to your charity, just so you know).
Many thanks.
obviously not 100%Apart from that i think it's working 100% :D
Do you mean the formula in Data sheet give the errors? That end up transcribing to column S?
Try adding the Error trap from before
CSE Confirmed!=LOOKUP(REPT("z",255),CHOOSE({1,2},"",LOOKUP(2,1/(ISNUMBER(MATCH("*"&A$2:A$4&"*",LEFT(AWorkbookin!$A2:$O2,255),0))+ISNUMBER(MATCH("*"&A$2:A$4&"*",RIGHT(AWorkbookin!$A2:$O2,LEN(AWorkbookin!$A2:$O2)-255),0))),B$2:B$4)))
Last edited by NBVC; 09-09-2011 at 03:19 PM.
Yep.
Looks like it is now :D
Thank you again for providing so much help - really really appreciated.
Yay!
.
.
.
.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks