Is it possible to combine Vlookup & Max?
In column A I have product codes some of which are repeated, in column B I have costs for the relevant product codes.
I want to vlookup the product code on another sheet, then Columns A & B picking up the cost in column B but if there are mutiple entries I want to pick up the highest cost out of all the entries with that product code.
I need to lookup two different columns and if values match, then bring back one value:
For Example:
Data:CityNameAtlantaDaveNew YorkCharlieLos AngelesDanSt. LouisDanMemphisSteven
Lookup Table:CityNameCodeAtlantaDaveDAVNew YorkCharlieCHALos AngelesJavierJAVSt. LouisDanDANMemphisRobertROB
The formula I'm looking for would produce the following results:
If City on Data matches the city on Lookup Table, and the name on data matches the name on Lookup Table, then bring back the code on Lookup Table.
Result would be:
Results:CityNameResultsAtlantaDaveDAVNew YorkCharlieCHALos AngelesDanWill be blank, as the names on Data and LookUp Table don't matchSt. LouisDanDANMemphisStevenWill be blank, as the names on Data and LookUp Table don't matcm
I have a file with two work sheet, in 1 sheet have monthly allowance to staff in 2nd sheet I need the data in a schedule format. Please see the attached file, my formula not working here properly.
View 6 Replies View RelatedLet's say for instance I have a vlookup that displays a the quantity of available items in stock pulled from a column in another workbook. Occasionally these numbers are negatives as they are backordered I would like to display these values as zero.
View 2 Replies View RelatedI have an Excel workbook containing a number of worksheets. My problem is that on one sheet(BlkPick) I have Rows containing headings(Location Names)and a column containing Product names. I wish to lookup the corresponding value contained at the matching intersection on another worksheet. I have searched this forum along with google and found that INDEX & MATCH are what I should be combining with Vlookup and these work when used on the same worksheet but when I test using say MATCH alone I get an error when attempting to MATCH my result from a different worksheet.
View 9 Replies View RelatedI am using =max(D:D), & need to return the cell address (using address)
However, then i need to combine this into my vlookup
I have been trying -
=VLOOKUP(ADDRESS(MAX(D:D),4),D:E,2,0)
LEFT(C4,3)
VLOOKUP(C4,C4:D38,2,FALSE)
VLOOKUP(LEFT(C4,3),C4:E38,2,FALSE)
I combined the first two formulas into one (the third one) but it is not working. I get an N/A result.
I need a formula that will combine hlookup and vlookup to return one value.
View 14 Replies View RelatedIs there any way to combine the VLOOKUP and IF Statement formula's into one
formula or is there any other kind of formula which does both?
I'm looking for a formula to return the correct value without using any help-column and it should be typed in no more than one cell.
View 10 Replies View RelatedI think I need a macro to do this. Can I use vlookup function for a range of number? lets say I have number 1001 to 1005. i want to combine the result. any other way other than using this formula
=vlookup(1001,table,2,false)+vlookup(1002,table,2,false)+vlookup(1003,table,2,false)+vlookup(1004,ta ble,2,false)+vlookup(1005,table,2,false)
Is there possibility to define a function that works like say =clookup(numlow,numhigh,table,2,false)?
I have a workbook with two sheets that I need to combine some of the data from. Sheet1 is a template to be used for translation purposes on Sheet2. I would like to put Sales Rep Info in column C on Sheet2 (has only Sales Rep ID) using Sheet1 as a translation table (shows Sales Rep ID and Sales Rep Info). I can do this with a vlookup function but would like to know how to do it in a macro so I can use on other more complex workbooks. Here is a sample data set.
View 3 Replies View RelatedI want to combine both an 'if' and a 'vlookup' within a single cell. So I want to say if the value in cell a1 can be found in columns c1:d100 then return a 'yes' if not return a 'no'. How do I do this
View 7 Replies View RelatedWhat i want to do is to look up of the value of home and away games seperatly. if there is no match for the lookup i get the "N/A" and if there is no value i get "0". If i get 0 the formula will calculate as a lost game.
View 10 Replies View RelatedXL 2003
I want to combine these three functions
=VLOOKUP(e4,AGTNAME!$A$1:$E$3086,5,0)
=LEFT(E4,FIND("/",E4)-1)
and iserror
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
I have a sheet using VLookup to find EMail and Web addresses. I can get the address to show up but not as an active URL address. Is it possible to have the address "active" so I can click on it and activate the EMail or Web Site?
View 5 Replies View RelatedI'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries
Mar
Apr
May
Jun
Loans to banks
Mar
Apr
May
Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.
View 4 Replies View RelatedI’m trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that month’s interest payment. Can anyone help me figure this out?
The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. It’d also be great if the formula can be transferred between workbooks. I’m not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.
I have data in a pivottable which I want to compare with another table. The lastcolumn+1 of the pivottable needs to get data from another table. I have this for an example:
View 5 Replies View RelatedI have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.
This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.
I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:
I have 12 workbooks (one for each month), they have a list of assets to our company and a running total of all expenses for the month that they incurred. The 13th workbook is a yearly analysis of each asset. I need to transfer all charges into this worksheet.
So for example:
Worksheet 1 (January)
Column A Column H
Asset # Total Expense
5021 $3,041.52
970289 $242.08
Worksheet 13
Column A Column B Column C Column D
Asset # Asset Description January February
5021 Rivet Machine $3041.52 $345.65
So hundreds more machines for twelve months can take awhile to populate this info and very confusing when dealing with separate worksheets.
I need to get 5 small subs into 1 sub. (run the whole program by pressing once.
Please see attached file with codes : ServiceDriftMaaling.rar
Ok, These are the three formula's. How do I combine them or can I.
=IF((B2="GOV"),[@[Gal Billed]]/1000*1.5)
=IF((B2="NPROFIT"),[@[Gal Billed]]/1000*2,0)
=IF((B2="COM"),[@[Gal Billed]]/1000*3)
I need to write IF statement with 4 criteria, or at least I think IF will do the trick. It would be difficult and not very visual to describe my question here, therefore I have attached a dummy workbook with the comments. Basically I have got 2 values in 2 columns. Next 2 columns will hold letter "x". There are 4 possible variations how "x" will appear in those two columns: first, second, both or none. So depending where the "x" is, I need to return one of the 2 values.
View 6 Replies View RelatedI have two formulas that i need to combine as one, but i dont now how.
This is the other formula:
=SUMPRODUCT((Huollot!I2:I500="Ty”n alla")*(Huollot!K2:K500="Warranty")*1)
The other one:
=IF(NETWORKDAYS(Sheet1!C1;Huollot!C2:C500)>20;1;0)
the biggest problem is that this formula does not work as it is. Or is there some other function than networkdays that i could use?
The purpose of the formula is to count how many "objects" that are in service have been there for over 20 days. There a a few other criteria also, but sumproduct takes care of that. Can networkdays even be used the way i´m trying to?
In cell B1 I have the number 1
In cell C1 I have the number 1031000
I know how to combine the cells in A1. I am getting a result of 1-1031000
What I would like to happen is that 1 becomes 001.
Ending result is 001-1031000
problem is that cells in B could be one, two or three digits.
On a small scale I could fix manually but i have over 15,000 rows to deal with
I want to combine number in one row.
Column A
12
13
14
15
Need to be in column B. 12,13,14,15. I tried “=A1&”,”&??
Trying to write an IF query looking for multiple returns that would equal the same result.
I have a series of number where the last digit would result in the same return if the digit were 1 or 6, 2 or 7, 3 or 8, etc.
1 or 6 = 1
2 or 7 = 2
I have been trying unsuccessfully to combine the IF and OR queries