Teach Yourself Excel Lesson VLOOKUP Formula - Vlookup Formula

I have a standard vlookup formula.

Code:

VLOOKUP(A2,'Prefered Suppliers'!A$2:B$53,2,FALSE)

But the source list will eventually grow. But in the formula, the range is fixed. Is there a way via VBA to monitor the source so that when another row is added (row 54), the formula updates to reflect 54 instead of remaining at 53?

Can you have a vlookup look information up in 2 different sources and only pull information from the source that has data in it (the other one would have empty cells)?

ie MasterSpreadsheetTestTypeA.xls
MasterSpreadsheetTestTypeB.xls

a user will only pick 1 spreadsheet to type text into when the run a test. I'd then like to be able to open a SummarySpreadsheet.xls to collect all the test data into. Basically they would open the SummarySpreadsheet and type in the Unique TestID Name into column A and then columns B thru X would automatically fill in (based on the data that was typed into the appropriate MasterSpreadsheet...).

On a sidenote, after the user fills in all the appropriate data into the MasterSpreadsheet, they'll be doing a "file, save as" and giving it a unique file name to include the TestID name. How can I get the SummarySpreadsheet to take into account this new filename without having to modify every cell's vlookup formula with the unique file name ? Is there a macro that can be run to take whatever text is in column A and add it to the file name in the vlookup formula ?

Hope I'm as clear as mud.
Thanks in advance for your assistance !

I've pick up an example for selecting date ranges which works only to the extend that it returns the grand total B2:B20. However, there is another part to the formula that I need to add which is to define a specific lookup of a name in a cell that matches the criteria in the Range F2:F20 to return only that sum for the range of dates.

Any ideas on how I can add another dimension to this formula?

To sum for a range of dates:
D2 = start date like 1/1/2009
E2 = end date like 1/5/2009
=SUMPRODUCT(--(INT(A2:A20)>=D2),--(INT(A2:A20)

Hi Guys,

New to the forum, and must commend the great help its been over the years, but ahve a question which i cannot find teh answer to, regarding an IF + Vlookup + Sum Formula.

Here is the sheet i am working with,

Cost Table Day 1 to 3 Day 4 to 8 Day 8 or Greater
Adam $10 $20 $30
Ben $15 $30 $45
Chris $8 $17 $28
Dave $14 $29 $32
Ed $25 $28 $35
Fay $7 $17 $24
Gary $11 $21 $31
Harry $13 $22 $36

Days Worked Cost
Adam 2 ?
Harry 5 ?
Fay 9 ?
Ben 15 ?
Adam 22 ?
Gary 7 ?
Dave 4 ?
Chris 8 ?
Ed 3 ?



I need a forumla, that will be able to calculate the cost related to each person, based on the specific cost per day from the Cost Table.

So for example, if we take the second item, Harry.
The forumla, should look up the name Harry in the Cost table, then detect that the days worked for him is 5, then perform a calculation for his cost. ie. 3 days x $13 and 2 days x $22.

Have attached the excel file for better viewing.

Hope that is clear enough to assist me.

Thanks in advance.

All,
Thanks in advance for your help. Probably many ways to do the following, but I'm having a mental block.

I have a table with sales targets in row 1 (e.g., 200, 400, 600, 800, 1000), and corresponding bonus payments in row 2 (e.g., $2,000, $4,000, $6,000, $8,000 and $10,000).

In my spreadsheet I want to perform a lookup based upon actual monthly sales in row 10, and return the bonus payment in row 11 for the first month in which the sales exceeds the sales target in row 1.

So for example, if in June 2009 actual sales are 150, and in July 2009 sales are 205, I need a way to grab the $2,000 bonus amount in row 2. What is tripping me up is that in August 2009, sales will also be greater than 200, but I DO NOT want the formula to pull back the $2,000 as it was already achieved.

Let me know if I can clarify my confusing question.

Thanks,
Chad

I want to do a vlookup (referring to a range on another sheet) in a cell, if the value in the preceding cell is "y".

I have tried =if(B2="y",=vlookup(B1,Clients,3,false),""), where

B2 is the cell on sheet 2 that I enter y or n
BI is the cell on Sheet 2 containing the 'trigger' value
Clients is the named range in Sheet 1
and 3 is the column on Sheet 1 to look up. But this doesn't work.

Any suggestions?

Thank you.

I have a vlookup formula in C1:C300, but it doesnt update..
I've opened the "Worksheet"-file, pressed F9, Crtl+Alt+F9 on the file where the vlookups are, but it doesnt update.
The only thing that works is to manually select the cell where the vlookup is, press F2+enter.

Here's my code:

VB:

=If(ISNA(VLOOKUP($A$2:$A$361; 'G:\Matics\IFS\PCDS\Stock550\EoL Cijfers\[Worksheet.xls]Worksheet'!$C$2:$AT$471;34;FALSE));"";VLOOKUP($A$2:$A$361;'G:\Matics\IFS\PCDS\Stock550\EoL Cijfers\[Worksheet.xls]Worksheet'!$C$2:$AT$471;34;FALSE))

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



I've searched some forums and came across a post that said it might be that the "links" arent correct, and that you could change those in "Modify"-"Links" (or something like that, I have the dutch version of excel), its 2nd tab on the top, the 2nd last option, above "object".
Thanks!

Edit1: I've changed this code a bit, since I'm using the dutch excel version, but i forgot to transer the ";" into ",", so don't lmind those :P

I have a database in the form of a spreadsheet with the following column headings:
A=DATE (3 letter abbreviation for the month...jan, feb, mar, etc)
B=Client
C=City
D=State
E=Department
F=Contract number

Is there a formula that I can enter in another worksheet (which is being used as a report), that will "grab" the monthly data for all entries for a given month (i.e. "mar"), and auto-populate corresponding cells with each? Essentially, I am trying to formulate a monthly report that will automatically populate the appropriate information for the month specified. I hope that I am being clear...any help is greatly appreciated!

I have to create a formula for my spreadsheet. I have four columns A - D

Column A & B (forms)
A has a name
B has an edition number

Column C&D (forms)
C has a name
D has an edition number

A&B represent a web location
C&D represent a different storage location

I have to cross reference A&B with C&D to see where my duplicates are.

I did a query in Access but, my boss wants it done only with Vlookup. I have tried to use the following formula =VLOOKUP(A2,$C$7:$D$219,1,FALSE) and it doesn't exactly work. So I what formula should I be using? How can I find my duplicates (has to be done with a Vlookup Formula) and should I be using a combination? Vlookup and IF?

Also, one problem I have had even with the above is that these columns were copy and pasted from two different spreadsheets. I have to retype the same information in the cell in order to get an accurate response. Even though nothing is different? Font, spacing, location, etc. It's all the same.

Thoughts??? PLEASE PLEASE HELP!!!

Hi,

I'm using a formula below, but when i am checking the results in my data the values in my excel report is wrong. I should be getting the correct values (see table 3).

My formula
=IF(ISERROR(VLOOKUP($A5,'[Inventory Trans by Item A 1.xls]Sheet1'!$A:$O,3,FALSE)),VLOOKUP($B5,'[Inventory Trans by Item A 1.xls]Sheet1'!$A:$O,3,FALSE),VLOOKUP($A5,'[Inventory Trans by Item A 1.xls]Sheet1'!$A:$O,3,FALSE))

Table 1 My Report
Item Alternative Items Item Signal Usage 2009-06 Usage 2009-07 Usage 2009-08 Usage 2009-09 Usage 2009-10 Usage 2009-11 Usage 2009-12 Usage 2010-01 Usage 2010-02 Usage 2010-03 Usage 2010-04 Usage 2010-05 00158476 123456 02 0 30 0 0 0 0 0 0 16 7 10 0 123456 0 30 0 0 0 0 0 0 16 7 10 0
Table 2 Inventory Trans by Item A
Sum of Quantity Actual date Item 2009-06 2009-07 2009-08 2009-09 2009-10 2009-11 2009-12 2010-01 2010-02 2010-03 2010-04 2010-05 2010-06 123456 30





16 7 10
2 1526204









1
1526226

2








1541258

4






2
1557019
4










Table 3 Correct Values
Item Alternative Items Item Signal Usage 2009-06 Usage 2009-07 Usage 2009-08 Usage 2009-09 Usage 2009-10 Usage 2009-11 Usage 2009-12 Usage 2010-01 Usage 2010-02 Usage 2010-03 Usage 2010-04 Usage 2010-05 Current Month 00151R0780 151R078 02








151R078
30




16 7 10
2