VLOOKUP issue #2

If I use a vlookup function, can I have three result cells? In othe
words, if the value in a1 is found in the lookup table and there ar
three columns (offsets) that could be used as correct answers, can 
display all three

--
Message posted from http://www.ExcelForum.com

0
6/24/2004 7:01:37 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
278 Views

Similar Articles

[PageSpeed] 15

Yes, you can use

=VLOOKUP(A1,Lookup_Table,{2,3,4},0)

assuming you don't want the value from the leftmost column, you need to
select 3 columns across
(like if you would select E2:G2 with E2 as the active cell), then enter the
formula in the formula bar and instead pressing enter press ctrl + shift &
enter

or you could use

=VLOOKUP($A$1,Lookup_Table,COLUMN(B:B),0)

put that in one cell and copy it across 2 more cells

note that I made the lookup value absolute or else it would change from A1
to B1 etc

-- 

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


"Barrym >" <<Barrym.18dckn@excelforum-nospam.com> wrote in message
news:Barrym.18dckn@excelforum-nospam.com...
> If I use a vlookup function, can I have three result cells? In other
> words, if the value in a1 is found in the lookup table and there are
> three columns (offsets) that could be used as correct answers, can I
> display all three?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
terre08 (1112)
6/24/2004 7:18:03 PM
Barrym wrote:
> *If I use a vlookup function, can I have three result cells? In othe
> words, if the value in a1 is found in the lookup table and there ar
> three columns (offsets) that could be used as correct answers, can 
> display all three? * 

Hi,

As an example, if you have the following table on Sheet 1,

Sheet 1
---------

Red	         1	2	3
Blue	         4	5	6
Green   	  7	8	9

and you want to return mutliple results for a lookup value, say Blue
then on Sheet 2, assuming that your lookup value is in A1, selec
B1:D1, and enter the following array formula:

=VLOOKUP(A1,Sheet1!$A$1:$D$3,{2,3,4})

to be entered using CTRL+SHIFT+ENTER,  and you'll get the followin
results:

Sheet 2
---------

Blue	        4	 5	6

Hope this helps

--
Message posted from http://www.ExcelForum.com

0
6/24/2004 7:26:39 PM
Barrym wrote:
> *If I use a vlookup function, can I have three result cells? In othe
> words, if the value in a1 is found in the lookup table and there ar
> three columns (offsets) that could be used as correct answers, can 
> display all three? * 

OK I'm a bit thick but I can't get this to work!

Suggestions

--
Message posted from http://www.ExcelForum.com

0
6/24/2004 8:03:23 PM
Ok, first layout a table on Sheet 1 like the one I gave for an example.

So,

A1=Red
B1=1
C1=2
D1=3

and continue to complete the table.

Then switch over to Sheet 2 and enter Blue in A1.

Then select cells B1 to D1.  So you now have three cells highlight, an
Blue as your lookup value in A1.

Press = and enter this formula:

=VLOOKUP(A1,Sheet1!$A$1:$D$3,{2,3,4},0)

Then press, altogether, the following keys:  CTRL+SHIFT+ENTER

Does this help

--
Message posted from http://www.ExcelForum.com

0
6/24/2004 8:23:01 PM
Reply:

Similar Artilces:

outlook won't close #2
The knowledge center refers to this problem as OL98: Outlook Continues To Run After You Quit Outlook View products that this article applies to. This article was previously published under Q263530. When I close the program, it keeps running. The article says that Microsoft has a fix, but the fix isn't listed or shown as downloadable. It does say I can pay 35 dollars for the fix though! Does anyone have this fix or know where I can get it? If you apply http://download.microsoft.com/download/outlook98/Update/98/WIN98/EN-US/o98Attch.exe, then you would be above the version num...

Connectivity Issues with SQL Server 2005 in Workgroup Environment
Looking at the application event log, I find back2back the following two messages. Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: xxx.xx.xxx.xxx] SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: xxx.xx.xxx.xxx]. I am running a remote asp .net application which runs on xxx.xx.xxx.xxx and makes db connection to this server, where this application event was logged. The asp .net is using impersonation. Why am I gett...

If statement with a Vlookup
I am trying to build an IF formula, that first looks for the value in a column on another tab, if found leave blank, If not found, to display text (an "x"). I have tried variations of the following formula, unfortunately it says I have too many arguments for this function. =IF(ISNA(VLOOKUP(A2,'cardholder list'!A:E,3,FALSE))=TRUE,"x",VLOOKUP(A2,'cardholder list'!A:E,3,FALSE)= false, "") I have tried to interchange false inside the vlookup to true and not getting the desired result. I appreciate any suggestions you have. ...

Vlookup problems
I have Vlookups set up for a particular Spreadsheet. Each month I just drop the new data into the first column. Even though the number is formatted just like the previous month (i.e general, text, whatever) my vlookup formula shows #N/A. If i double click in the data cell and hit enter, the vlookup formula works. can you tell me why, or if there is a better way than clicking and hitting enter? Thanks! do you have your automatic calculation turned on? Under tools<options<calculation. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip...

RMS and PC Charge #2
Just got PC Charge setup on the register here today. We are using RMS as our POS. Is there a setup between the two or does RMS automatically know to look for PC Charge. The PC Charge people told me to call my RMS dealer. Yeah, wish I had one of those. Any help would be appreciated. C Sharks fear you, Could you send your email address + your question to Michelle Goodman and ask her to forward it to me? Thanks, Kevin Pham -- This posting is provided "AS IS" with no warranties, and confers no rights. "Sharks Fear Me" <SharksFearMe@discussions.microsoft.com> wr...

MS Point of Sale 2.0
For opening and closing amounts, we keep a set amount of cash in the drawer. During business, cash and checks are accepted. At the end of day, we are doing a "drop" of the amount of cash AND checks added together (since POS does not have a position to do "check drop". We still balance overall but the z report indicates the overage/shortage of the check amount in the close section. Is this the correct way to close? Should there be position to enter the amount of checks that we are dropping? If we leave the amount of checks in the closing amounts window, will POS ...

Scatter Chart Question #2
Hi Folks: I've made a scatter chart that looks the way I need it to. When I "hover" the mouse over the data points, I get the point values as I expect in the yellow box. My question: Instead of the data point values, is it possible to get a picture in the pop-up ?? Many thanks Steve You don't have a lot of control over the content of these chart tips, beside turning them on and off. If you want to customize the labels, you can download the Chart Hover Label add-in from Tushar Mehta's web site (http://tushar-mehta.com). To show a picture you will have to r...

Saved Queries #2
We are using Exchange Server 2003 and Windows Server 2003. On our DC, I created a number of Saved Queries that provide a list of members for our mail-enabled distribution lists. Right now the only fileds that appear in the result is the Name and Type (contact, user). In order to get the Display name and Email addresses to appear, I have to go to View --> Add/Remove Columns and add them that way. Is there a way to use the AND operator when setting up the queries? Also, I found out that you can export the query definition and then import it but you can't export the saved querie...

The page cannot be found #2
Exchange 2003, OWA 2003 &Outlook 2003. Outside company premise, when using OWA to read mails, some mails cannot be displayed. It shows "The page cannot be found". When read mails in company premise (LAN connectivity) using either OWA or Outlook, it has no problem. What goes wrong???? We have a customer with the exact same problem. Certain messages can not be displayed in OWA from outside, while those same messages can be read from inside using Outlook or OWA. Dean "Emyeu" wrote: > Exchange 2003, OWA 2003 &Outlook 2003. > Outside company premise, when...

Refresh problem for the size grip with Windows XP style #2
Hi, I have a dialog application with a scrollbar size grip. On Windows XP with the visual style, there is a refresh problem for the size grip region when I move the application out of the screen; only if I go to the bottom of the screen. It's OK if I go out by the right side. Without the XP style, everything is OK. I have noticed the same behavior with Notepad. Do you know how could I solve this problem? Any ideas will be appreciated, Thanks It's a known problem in XP (non-client area is erased incorrectly between scrollbars). Will it ever get fixed? I would not bet on it. &qu...

selected text highlighting issue
Windows XP SP3. Office 2003 fully updated. This has always bugged me so I figured I'd finally ask the question. I run 2 monitors and work on multiple things at once. If I have a Word doc open (or notepad or some other editor) and select a block of text and then move to another application, the text in the Word doc no longer appears selected. This is extremely annoying at times. Just because my Word doc is no longer the active application doesn't mean that I don't want to see what I've selected. And if it's so that I know it's not the active application, well t...

Excel Combo Box #2
I have created an Excel Combo Box that include ten different selections. The combo box is linked to a cell. I want the selection in the combo box displayed in the linked cell. What I get know is the line number. How do I correct this? Instead of using the combo on the forms toolbar, try using the combo on the control toolbar (View/Toolbars/Control Toolbox) and then set the look up range and linked cell from the combo's properties. HTH Paul >-----Original Message----- >I have created an Excel Combo Box that include ten >different selections. The combo box is linked ...

dropdown menu #2
I have a list of over a thousand names in a dropdown menu. I would like to start typing a name in the cell (in the dropdown menu) and have excel automatically recognize the name and go to that name in the dropdown menu before I even finish typing the full name. How do I do this? thanks. Use a combobox, it works that way. -- HTH Bob Phillips ""PA via OfficeKB.com"" <forum@OfficeKB.com> wrote in message news:50EBF401BCE61@OfficeKB.com... > I have a list of over a thousand names in a dropdown menu. I would like to > start typing a name in the cell (in the dr...

Data Validation
What I am trying to achieve is a drop down list that would look something like this: D1a - suipplies F1g - cell phones C2a - travel E4b - computers and so on and so forth. I can do that. What I would like to happen though is in the same cell that the drop down box is in (a29) I would like to select one of those options and only have D1a, or F1f, ect show up in the cell. The words are just references to my budget, and I don't nned them after I selected the right one. Is this possible? I am somewhat new to excel and have never went into the visual basic editor and I don't have...

Windows Live Mail Issues...
Hi there... I have been using Windows Live Mail to access my hotmail and yahoo email accounts from my desktop successfully, without any probs, for about a year now. Two days ago I suddenly started getting 'please enter your user name and password for the following account' for yahoo over and over and over again. I enter it and the message just pops back up. Any ideas??? I've checked all the POP settings - all fine. A few details... using Windows XP home edition. Yahoo Plus! account ? U.S. Based account ? Account accessible at http://mail.yahoo.com/ ? ...

Outlook/Hotmail synch issue
I just installed Office Ultimate on my new PC. I am trying to set up my hotmail account within Outlook. I have a hotmail folder now showing in Outlook, but it is empty and will not synch. Help! Have you already installed the Outlook Connector? http://www.microsoft.com/downloads/details.aspx?familyid=9a2279b1-df0a-46e1-aa93-7d4870871ecf -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "...

vlookup or other function
I have two sheets with data on them. Sheet two contains item numbers and monthly usage: Item Usage Month ABC 8 1 ABC 0 2 ABC 2 3 DEF 5 1 DEF 2 2 DEF 1 3 Sheet 1 contains the item numbers and I want to add two columns Avg and Total. Is there a function that I can put on sheet one to total the usage and another to average the usage from sheet 2? Dan Im not sure if this answer is what you mean but.... =average(c2:c8) =average(sheet2!c2:c8) Im not sure if I am unders...

COleDateTime::GetCurrentTime() problem #2
Hello, I have ported a VS.NET 2003/MFC7 application to VS 2005/MFC8 and encountered a problem with COleDateTime::GetCurrentTime() which worked fine with MFC7. Sometimes I get an invalid COleDateTime object from GetCurrentTime() (m_status equals COleDateTime::invalid). I tracked the problem down to AtlConvertSystemTimeToVariantTime() which is called during conversion from SYSTEMTIME to double time representation. AtlConvertSystemTimeToVariantTime() calls SystemTimeToVariantTime() to convert from SYSTEMTIME to double. VariantTimeToSystemTime is used then to convert the result back to SY...

Unable to relay #2
I added a new domain to the recipient policy and everything works fine if I send mail to it from the server itself but when I try to sent from an external e-mail address it comes back with the "unable to relay for newdomain.com" message. Why would that be? The server is running Exchange 2000 with all the latest sp's and updates. The mailbox that I'm sending to lives on another server running Exchange 2003 (+latest updates) that's in the same routing group. Port 25 on the 2K3 system is not open to the outside world so all mail for mailboxes living on the 2K and ...

Switching Profiles #2
This is a multi-part message in MIME format. ------=_NextPart_000_0010_01C42602.CE48E3D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have outlook 2002. I created 2 profiles, one for personal and one for = work. I have not been able to figure out how I can switch from one to = the other without closing outlook and going back in a gain. I have read = the posts in this newsgroup, but I do not see it. If it is here, can = some please direct? Thank You! Deb ------=_NextPart_000_0010_01C42602.CE48E3D0 Content-Type: text/html; charset=&q...

Excel 2007 Read-ony issue
recently after I opened an excel file that I have been using for sometime now. I made some modification to the content of a spreadsheet. When I attempted to save it i get a read-only restriction. i attemtp to save as under a different name and it does it again. now I cant save my changes and I dont want to loose my work. I have tried clicking on the properties box and have removed the check in the read-only box. then close but it continues. last time I ended up cutting and pasting the excel into a word document just so I could save it. any ideas?? i have tried to save it in the 2...

Macro Help #2
Hi All In a workbook I have a worksheet 1 that looks something like this A B C Code Name Position 1 Smith MD 2 Jones Sales 3 Brown R & D 4 Bloggs Driver etc etc etc Worksheet 2 is an individual form as below that where I type in the code, the rest is automatically populated with name and position. There are more details underneath but the heading of the form is automatically filled! A B C D Code ...

template buddy #2
:( but it's too complicated and time-consuming that way!!! i already mad the templates to show what my problem is!!! *cries -- medicenpringle ----------------------------------------------------------------------- medicenpringles's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1645 View this thread: http://www.excelforum.com/showthread.php?threadid=27832 Hi o.k. then maybe someone else will help you Most people just don't open files from unknown sources. Sorry -- Regards Frank Kabel Frankfurt, Germany "medicenpringles" <medicenpringles...

Before You Buy Accounting Software for Your Small Business #2
Buying the right Accounting Software for your business is very important. Because you will be using your Accounting Software frequently, it's urgent to ensure that you pick the right software for your business and your needs. Accounting software can be a great tool for your small business, allowing you to keep track of all your financial data, and easily produce reports for effective planning. Here are some things you should think about, before buying your Accounting Software: http://smallbusiness-accounting-software.blogspot.com ...

Is it possible to have 2 different character sets in subject line
Hi, I'm using outlook. Is it possible to have 2 different character sets in the subject line of an email. The following subject line with encoded words in UTF8 and ISO-1022-JP is corrupted when open in Outlook. =?UTF-8?Q?Out of Office =E7=A7=81=E3=81=AF?==?ISO-2022-JP?B?GyRCNkMkLC?= The first part, which is in UTF-8, contains some Japanese characters that are corrupted in display while the second part (Japanese characters) displays correctly. In my situation, I need to be able to handle exactly 2 different character sets in the subject line. Does any one know what's the correct wa...