 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008VLOOKUP Value that is Repeated
Hi I am so struggling today. I am doing a Vlookup on sheet 1 cell A1 which contains the word "Hospital". On sheet2 I have a column with all of our companies' jobs in column A. The problem I am running into is that we will submit information several times on the Hospital job. So none of the entries are complete but together between all 5 or 10 entries if you combine them you get a complete entry. Is there a way to do a lookup and if the value in sheet2 cell B1 is blank have it look for the next entry and if that one is blank continue until it finds a value? Thanks in advance for your help!!! Hi, I think you say that of the multiple "Hospital" entries in Column A, only one of them will have an entry in Column B. If this is so, try: =SUMPRODUCT(--(A3:A100=A1),(B3:B100)) You would have to insert the name of the other sheet (eg 'Sheet(1)!'A3:A100) Regards - Dave Is the value to be returned text or numeric?  SignatureBiff Microsoft Excel MVP > Hi, > I think you say that of the multiple "Hospital" entries in Column A, only [quoted text clipped - 4 lines] > 'Sheet(1)!'A3:A100) > Regards - Dave Depends on the Column. Some are numeric some are Text. Like address, phone number, names..... > Is the value to be returned text or numeric? > [quoted text clipped - 6 lines] > > 'Sheet(1)!'A3:A100) > > Regards - Dave Try this... Returns the first non-blank cell that corresponds to the lookup value: =INDEX(Sheet2!B1:B10,MATCH(1,INDEX((Sheet2!A1:A10=A1)*(Sheet2!B1:B10<>""),,1),0)) SignatureBiff Microsoft Excel MVP > Depends on the Column. Some are numeric some are Text. Like address, > phone [quoted text clipped - 11 lines] >> > 'Sheet(1)!'A3:A100) >> > Regards - Dave Does this only work with Numeric answers? The answers are both. > Hi, > I think you say that of the multiple "Hospital" entries in Column A, only [quoted text clipped - 3 lines] > You would have to insert the name of the other sheet (eg 'Sheet(1)!'A3:A100) > Regards - Dave |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.