lookup confusion

Hi -- Can someone help me with this formula?
=INDEX(INDIRECT("'"&DealPlatformType&" 
Platform'!I90:I98"),MAX(1,MATCH(Planner!DealBlockSize,INDIRECT("'"&DealPlatformType&" 
Platform'!F90:F98"),1)))+VLOOKUP(Planner!DealBlockSize,INDIRECT("'"&DealPlatformType&" 
Platform'!F90:H98"),3)*(Planner!DealBlockSize-(VLOOKUP(Planner!DealBlockSize,INDIRECT("'"&DealPlatformType&" Platform'!F90:I98"),1)))

I need it to only lookup a value now and not do do so much so for example 
someone inputs  a size and it goes to another tab based on which platform 
they choose and then picks the price directly across from it
so it someone picks 250 it should go to the right tab and pick 250 and then 
the price directly across from it?  

Can anyone help?

Thank you
0
HS (21)
5/19/2009 3:59:01 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
403 Views

Similar Articles

[PageSpeed] 18

It sounds to me as if you're looking for a very simple form of the VLOOKUP() 
function.

Lets say on the sheet where you make the size selection and want to return 
the value from another sheet that:
the size they select goes into column B and you want the looked up value to 
show up in column C.  

Meanwhile, over on a sheet/tab named "myTableSheet" you have information 
laid out with the sizes in column A and several columns of information and 
then the price is out in column F.  This table goes from row 1 to row 1005 on 
that sheet.

In C2 on the first sheet you would put this formula:
=VLOOKUP(B2,'myTableSheet'!$A$1:$F$1005,6,FALSE)
what this says is to get the value in B2 on this sheet and then look on the 
myTableSheet in column A (from rows 1 through 1005) and find the first value 
in that column that matches - and when you do find the match, then return the 
value in the 6th column of that table, and the entries in column A don't have 
to be in order.

You can actually get Excel to do most of the work for you - again, using C2 
and the other limits I mentioned, you could click in C2 and type
=VLOOKUP(
and then click in B2 on the sheet
type a comma
select the myTableSheet and all of the cells in the table
type another comma 
type the 6
type another comma
type False
and type the closing ) and end it all with the [Enter] key.

Now, if the value in B2 is not in the list in column A on the other sheet, 
you'll get a #N/A error as a result.  You can hide that by wrapping the 
formula in an error trap like this:
=IF(ISNA(VLOOKUP(B2,'myTableSheet'!$A$1:$F$1005,6,FALSE)),"No 
Match",(VLOOKUP(B2,'myTableSheet'!$A$1:$F$1005,6,FALSE))

Hope this helps.  Excel Help for the VLOOKUP() function should give you more 
advice also.
"HS" wrote:

> Hi -- Can someone help me with this formula?
> =INDEX(INDIRECT("'"&DealPlatformType&" 
> Platform'!I90:I98"),MAX(1,MATCH(Planner!DealBlockSize,INDIRECT("'"&DealPlatformType&" 
> Platform'!F90:F98"),1)))+VLOOKUP(Planner!DealBlockSize,INDIRECT("'"&DealPlatformType&" 
> Platform'!F90:H98"),3)*(Planner!DealBlockSize-(VLOOKUP(Planner!DealBlockSize,INDIRECT("'"&DealPlatformType&" Platform'!F90:I98"),1)))
> 
> I need it to only lookup a value now and not do do so much so for example 
> someone inputs  a size and it goes to another tab based on which platform 
> they choose and then picks the price directly across from it
> so it someone picks 250 it should go to the right tab and pick 250 and then 
> the price directly across from it?  
> 
> Can anyone help?
> 
> Thank you
0
JLatham (219)
5/19/2009 9:44:05 PM
Reply:

Similar Artilces:

Lookup, vlookup, find, or what
Thanks in advance. Xcel07 on WinXP. I have a list of names in sheet 4, I need to search sheet 1, 2 and 3 to see if each name on sheet4 is located on any of the other 3 sheets. Here are a couple of my attempts so far =LOOKUP(C3,'Friday 930am:Monday 1130am'!C2:C25) =SUMPRODUCT(C2*'Friday 930am:Monday 1130am'!C2:C25) To clarify the above Sheet 1 is named Friday 930am Sheet 2 is anmed Friday 12:30pm Sheet 3 is named Monday 1130am Any suggestions are welcome You can't do that type of 3D referencing. Need to individually look through each sheet =ISNUMBE...

Lookup two columns
I want to compare the contents of two (adjacent) cells in one sheet with two adjacent cells in another sheet (within one workspace) and if the *pair* of cells are the same, deliver the value in the cell a few columns along (if you know what I mean - like lookup but comparing two cells). The cells are not sorted. Any ideas? Cheers. Bobby If you are comparing A1-B1 sheet 1 to A1-B1 sheet 2, then =IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!B1=Sheet2!B1),"They match","no match") If you have to "lookup" A1-B1 against the whole columns of A and B on sheet2, then kyou co...

Nest Table Lookup
Is it possible to nest together a lookup. I have it now where I lookup a random number in a table and it provides a value, numbers 2 and 12 require another lookup in another spot in the table. Not quite sure how to do that I have this, so I can recognize a 2 or 12, do I just add another lookup to the "true criteria" (where the word "Lookup" is? =IF(VLOOKUP($I2,Charts!$A$3:$C$13,2,TRUE)="II","Lookup","OK" Am I headed in the right direction. Please advise Thanks Dou ...

Advanced Lookups setup should let me exclude inactive records
Advanced Lookups setup should let me exclude inactive accounts, customers, vendors and employees as well as discontinued items. There should be an option in the lookup windows to include these inactive/ discontinued master records if desired. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree"...

Lookup window should default to first record in table
Right now if you click on the looking glass, it opens the window at the record that is already on the field prior to clicking the looking glass. It would be nice if it only did that on the change script so if you didn't change the field at all, when you open the looking glass it brings you to the first record in the table. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open ...

Lookup value in list and return value
This is probably something super simple. But for some reason, i cant figure it out. I have a list of dates in ascending order. (C2:C1000) 6/20 6/21 6/22 6/23 6/24 6/25 6/26 6/27 etc... In A:2, i would i want to enter a specific date that is provided in the date list above. 6/20 and return the value 10rows down. Any suggestions? =INDEX(C2:C1000,MATCH(A2,C2:C1000,1)+10) -- Kind regards, Niek Otten Microsoft MVP - Excel "J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in message news:1183486779.522088.129820@c77g2000hse.googlegroups.com... | This is probably somethin...

Case Lookup 04-21-06
Is there a way to prevent resolved cases from appearing in the case lookup? Specifically... Create an email in Outlook. Click the 'Track in CRM' button. Click the 'Regarding' button. Change the 'Look For' to Cases. Currently this lookup displays all cases with no restrictions. I would like to restrict this to only show active cases. ------=_NextPart_0001_2F86B88A Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Cindy, Thanks for the post. I will have to take a look at this to see if there is a way to filter the view. A quick glance at the issu...

Named Range and Lookup
Hello In a sheet called Birthdays, I have a Range of cells, A2:C25 which has a list of all my kids, nephews & neices in one column, their birth dates in another and their ages in the 3rd. I've called this Kids. In another sheet I've got a kind of journal of dates of events and stuff the family have done etc. I want to put along the age of the kid they each refer to but without having to keep trying to figure it out as there are a lot of kids. With the Insert Functions wizard , I've created a lookup formula that goes =VLOOKUP(Q11,Birthdays!$A$2:$C$25,3) except some birthday...

Lookup limits?
My object is to automate a form report of pre worded statements generated from a code that selects &#8220;YOU HAVE&#8221; statements # 1-27 and &#8220;WE RECOMMEND&#8221; statements # 28-62 from &#8220;Table1&#8221; displayed in cells to be merged in a table named &#8220;Data&#8221; I am using excel to be the data source document and every thing seems to work until statement # 30 then excel repeats statement #31 over and over regardless of the code number above #27 entered. Excel sheet &#8220;Table1&#8221; is numbered consecutively 1 to 62. Sh...

VLookup using lookup table
I have two spreadsheets within a workbook. One one sheet, there is a data table; the other, a form. The form has a few validation listings. The first validation list pulls information from the data sheet. Ideally, what I want to happen is that when a selection is made from this list, a second list is populated. I have created range names for the information in the data table sheet along with a lookup table and have the validation source set as: =INDIRECT(VLOOKUP(F20,tblLook1, 2, 0)). For instance, if the option of DEPARTMENT is made in F20, the list should be populated wit...

month lookup
i have the following formula =IF(MONTH($B:$B)=MONTH(J1),+SUMIF($H:$H,"auto",$F:$F)+SUMIF($H: $H,"auto",$E:$E)) problem is i am getting results (sum) of all of column F and E I only want the amounts in F and E if the applicable row is the month of jan B E F H 01/01/07 200.00 55.00 auto 01/15/07 201.00 0.00 auto 02/01/07 100.00 25.00 auto 02/15/07 125.00 35.00 milk So for Jan - i should get 456.00 What am i missing here. Try it like this: =SUMPRODUCT(--(MONTH(B2:B5)=1),E2:E5+F2:F5,--(H2:H5="auto")) A couple of notes. ...

Lookup #14
I ahev a workbook with 20 sheets in it, all template forms for completed jobs. On the first sheet I have the date in cell a2. b4 back dated 1 year from a2. I need to lookup cells c2:d2{(merged)this is a completion date}. in all the 19 remaining sheets and show the date if it is within the month back dated 1 year. sheet 1 a2= "September 30, 2005" with formula (=today()) sheet 1 b4= "September-04" with formula (=a2-365) sheet 2 c2:d2="September 30, 2004" or the same as above only in sheet 19. the fromula i have now is =VLOOKUP(B4,'Job 1'!C2:D2,TRUE) an...

Item Lookup Security
Is there a way to setup the user security for the item lookup button (for sales transactions) so that a user is able to see only a selected/ limited list of items and not ALL items? Aman, Currently this doesn’t exist out of the box, but it could be customized in several ways as described below: 1. If you are familiar with Dexterity you can follow David’s proof of concept about data level security in his post below: http://blogs.msdn.com/developingfordynamicsgp/archive/2008/08/13/row-level-filtering-proof-of-concept.aspx 2. You can create smart-list filtered for the needed...

Modifying item lookup window
Is it possible to modify the item lookup window to show Description by default instead of Short Description? I know users can get to the description on that window with an additional click, but when they are doing lookups frequently that's a lot of extra clicks. Thanks. -- Jim@TurboChef Hi Jim, There is a product called SmartFill that will allow you to create the lookup window for an Item anyway you want. It is relatively inexpensive, probably a little more then the cost of customizing the window. But it works with Items, Customers, Vendors, Account Numbers, Employees, etc. it a...

Lookups #2
I am trying to write a formula which looks up a product code in a list, and then references a date to pick out a piece of information. The code is in a horizontal list with the dates being in a vertical list across the top of the page. eg Code Dates Jan Feb Mar 111 50 70 80 112 90 10 60 113 60 10 80 It needs to refer to the code then the month, to be able to select the correct cell. eg code 112 in February = 10 Is there a function to cross reference the two pieces of information to select...

Help with Lookup/Sum Formula
I have a spreedsheet with this type of data, for example Product Group Quantity Size 2812 10 0.75 2812 100 0.75 2812 300 0.5 2817 100 0.25 2817 200 1 2845 1000 1.5 I want to be able to set up a formula that you enter a product grou (2812) in a cell and it will for that product group go and sum up al the quantities for each size. The formula would go in the Sum colum For Example ...

Lookup #2
I am using XL2000 and haven't been able how to create this lookup: I want to check if each cell in Range 1 E9:E25 is in the lookup range R9:R25. If it is I want to count the number of times "A" exists in F9:F25. There could be more than on instance of the Range1 value in the range. I figured I couldn't use a regular array (E9:E25=F9:F25) because the matches will not be in the same row I have tried to use match, but had problems because of multiple occurences in the lookup range. So I tried an array formula with Vlookup , but this total give me every A, regardless of...

perform dns lookup on incoming messages
Hi, Do I need to enable the "perform dns lookup on incoming messages" to be more secure? My domain01.local has no reverse dns but on my ISP I have one reverse ptr on my domain.com. Do I need to enable reverse dns on my domain01.local as well if I enable the above question? Please advise, Roto01 On Mon, 5 Feb 2007 09:10:02 -0800, Roto01 <Roto01@discussions.microsoft.com> wrote: >Hi, > >Do I need to enable the "perform dns lookup on incoming messages" to be more >secure? That won't make you more secure, and that feature doesnt work the way it ...

date lookup, summing numbers
hello everyone, I'm looking for some simple help (or i hope simple) on doing a date lookup in one column, then comparing it to a list of numbers and giving a sum. So I'd want to possibly use the Today()-10 function so it will go back ten days, and know I'm talking about March 4th, from that, I'd like to sum the numbers in another column and give me the total. ..... The background on this is to make my job easier, giving me the abality to know right away if any of my employees have any "points" for the last 10 days, 30, days, 50, days etc... I'll try to show a...

Table Lookup Help
I have a table that looks like the following: Col.A Col.B Col.C Col.D Col.E Row.1 4 3 2 1 Row.2 A 0.6 0.5 0.45 0.31 Row.3 B 0.55 0.51 0.42 0.32 Row.4 C 0.58 0.49 0.40 0.29 I have two values that exist in cell F1 and cell F2. The F1 cell contains the row to be selected and use =Match(F1,A1:A3,0) (exact Match Always). This tells me the row number th...

need help with lookup
I have a table of values like this from A1 to B4 A B 1 123 wall 2 254 chair 3 389 book 4 478 pen Now I want to write a function (maybe with "lookup") to look at the first tree characters in coloum A and return the value of it (here, writen in coloumb B) I have tried with this: A B C 1 123 wall =lookup(left(A1;3);A1:B4;2) 2 254 chair 3 389 book 4 478 pen but I get #N/A in C1 if I write =lookup(123;A1:B4;2) I get 2 where as I would like to get wall can anyone help pls? Hi Try using VLOOKUP() =VLOOKUP(123,A1:B4,2,FALSE) -- Andy. "khers Field" <field@yahoo.com&g...

Regardingid default lookup view
When selecting the regarding lookup the default view is to lookup 'accounts'. I would really like to know if and how it is possible to change the default to contacts. I have been looking all over and have not been able tofind that form anywahere to change the settings. Thank you very much! ...

Lookup for Assigned queue on Case Form
Hi, I have added a lookup field on the case form where I want the assigned queue to display automatically if it has been assigned to a queue. Everything works fine, except the automated population of the field when case is loaded. /Lotten ...

Lookup troubles
I am trying to get information from one worksheet into anothe worksheet. I have used the lookup function to do basically the sam thing all within the same worksheet, and it seems to work fine, bu when bringing it across worksheets, it doesn't wanna work. Here is th scenario: I have a labor table separated into levels of expertise. Each level ha a text name, and they are listed in column 2 (or B). In the firs column, I have a code number which correlates to each lever o expertise. All this resides on Sheet 3 ('Labor Values'). On sheet 2, labor is assigned in a small table where th...

Business Data Lookup 07-11-06
I have installed the Business Data Lookup add on which is supposed to allow CRM data to be inserted into office documents such as a Word file. Inside of Word 2003, I see a "Insert from CRM" button, but when I click on it, nothing happens. It is just clicked, but no options come up after that. Is this a bug? --- Brandon Office Equipment & Supplies http://www.presentationsdirect.com I think it is. I experienced the same. On top of that it will slow down MS Office heavily. Forget it. "Brandon" wrote: > I have installed the Business Data Lookup add on which...