 |
 | 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 / New Users / June 2007Vlookup question
I have to search and put values in one of my sheets after looking in a field of a field. I believe i need to to a nested vlookup, but im not sure how to go about it. I copied the relevant portions of the data below: Department PriceType Monthly Average NY NY Average $1,000.00 NJ NJ NJ NJ Average $1,500.35 I have to search within a department (for example NJ), and then search where average is occuring in the Price Type field, and then pick the corresponding Monthly average value(1500.35 in this case) and put it in the vlookup formula origination sheet. Any help? =INDEX(C1:C00(1,("A1:A100=NJ")*(B1:B100="Average"),0)) where column A is the dept, B the type and C month figures which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered.  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >I have to search and put values in one of my sheets after looking in > a [quoted text clipped - 19 lines] > in the vlookup formula origination sheet. > Any help? i tried it, it says that the formula has too few arguments... Try =INDEX(C1:C100,MATCH(1,(A1:A100="NJ")*(B1:B100="Average"),0)) still array entered SignatureRegards, Peo Sjoblom >i tried it, it says that the formula has too few arguments... Sorry, missed a function =INDEX(C1:C00,MATCH(1,("A1:A100=NJ")*(B1:B100="Average"),0)) SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >i tried it, it says that the formula has too few arguments... You missed a 1 as well INDEX(C1:C100 Peo > Sorry, missed a function > > =INDEX(C1:C00,MATCH(1,("A1:A100=NJ")*(B1:B100="Average"),0)) > >>i tried it, it says that the formula has too few arguments... the formula was working just fine till i changed the data set a bit. now even though i chaned it back on to the original, it isnt working fine. the formulla im using is pasted below: =INDEX(X!D3:D106,MATCH(1,(X!A3:A106=$A5)*(X!B3:B106="Total monthly Average"),0)) X is the name of a worksheet within my workbook from where im extracting data from. column D is the data i want to return column A and B are teh columns on the basis of which i want to return the data any possible reason for a #N/A error? You don't have a row in the X worksheet where A3:A106 matches the value in A5 and b3:b106 = "total monthy average" at the same time. > the formula was working just fine till i changed the data set a bit. > now even though i chaned it back on to the original, it isnt working [quoted text clipped - 10 lines] > > any possible reason for a #N/A error? SignatureDave Peterson i do have atleast 1 row matching for each value from A3 to A106 If that's the case then there are only 2 options, you didn't enter the formula with ctrl + shift & enter (should give you curly brackets around the formula), that will give you an N/A error or that what looks like a match is not SignatureRegards, Peo Sjoblom >i do have atleast 1 row matching for each value from A3 to A106 the bad thing is tht i did both... still this thing is hapenning... donno why... i do have 1 such row Pick out that row that you think matches and find a couple of extra cells and use a couple of formulas like: =X!A37=$A5 =X!B37="Total monthly Average" Where 37 represents the row you think matches your data. Do you see True in both cells? > i do have 1 such row SignatureDave Peterson |
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.