I am trying to create a VLOOKUP formula for the attached spreadsheet. What I want is on the Gen Shows tab, on the Last Data Update column (C), I want to add a LOOKUP formula so that when the corresponding named show from the July tab shows as being published (column E), it needs to bring back the date from column D.
Lookup With Inconsistent Data
-
-
-
Re: VLookup with text
=IF(LEFT(VLOOKUP(A2;July!$A$2:$E$38;5;TRUE);9)="published";VLOOKUP(A2;July!$A$2:$E$38;4;TRUE);"")
the names in both columns A must be identical or you will get an error
-
Re: VLookup with text
Without consistent data between the two, it may be tough. Initially, it appears you could use column A from GenShows to lookup the corresponding data on the July sheet column A. However, there are no matches as everything on the July sheet appears to be a filename (a .ppt file) and the GenShows is using just a title.
Also, what are you wanting to do if the title/show/file (column A) doesn't exist on the July sheet?
-
Re: VLookup with text
Something like this for cell C2, fill down also:
=IF(ISERROR(VLOOKUP(A2,July!$A$2:$E$38,4,FALSE)),"",IF(LEFT(VLOOKUP(A2,July!$A$2:$E$38,5,FALSE),7)<>"Publish","",VLOOKUP(A2,July!$A$2:$E$38,4,FALSE)))
Wigi
-
Re: VLookup with text
It can be done, at least in theory, but the structure of your data is going to make it more difficult than it needs to be.
Your shows have different values in the two sheets - the July sheet adds a .PPT extension, so a straight lookup won't work. You can append .PPT when you do the lookup, but could you not introduce some form of Show ID on both sheets so that consistency would be guaranteed?
You would need to search the text in July column E to find the text "published". If this is ever abbreviated or spelled incorrectly your formula won't work. Is there any reason why you can't have a column called "Published" and just enter a Y (or some consistent identifier) to determine whether it has been published?
You can make things much easier for yourself in Excel by structuring your data in a logical way. -
-
Re: VLookup with text
very good point! Assume all the names in both A columns are not file names therefore will match. If the show doesn't exist on the July sheet then I want it to leave it as it is currently
-
Re: VLookup with text
Quote from melsmiffIf the show doesn't exist on the July sheet then I want it to leave it as it is currently
With that statement, that (to my mind at least) says you can not use a formula in the cell but rather have to run a macro against it. Then, how you are you going to determine if what it typed in manually is better/more correct than the date on the July sheet?
Take a hard look at Batman's suggestions - he's a pretty smart guy. As it currently appears, you are making way more work for yourself than is really necessary.
-
Re: VLookup with text
Batman - thanks for your suggestions. I have now introduced a show ID for consistency and added a published Y/N column. Am still unsure as to how to use the VLOOKUP function to do what i need through
-
Re: VLookup with text
Could you post an updated copy?
-
Re: VLookup with text
updated copy
-
-
Re: VLookup with text
I did not see a ShowID in that sheet. Did you maybe attach the incorrect one? Otherwise, the previous suggestions for the lookup should work. It will however prevent you from manually typing something in those cells or you will overwrite the formula.
-
Re: VLookup with text
Yes, please share the formula used in the spirit of ozgrid, for 2 reasons.
1) Others that search locate the answer and not ONLY your problem
2) So I can change your Thread Title to something more helpful.
BTW, if you set up your data correctly, you can do all this and MUCH more via [pt]*[/pt]
-
Re: VLookup with text
I am quite happy to try a pivot table but I thought you could only use this with numeric values? The responses I need are text. Would this work somehow?
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!