The row in blue is a successfully created result from my VLOOKUP formula:
The row in red is unsuccessful.
=VLOOKUP($A$11,$A$3:$G$9,(COLUMN($B$2)))
The above formula lives in the blue "Pottery" cell (cell B11 in my spreadsheet).
How can I get this VLOOKUP formula to work in rows below it?
When I just copy and paste the formula into row 12 (in red text), the formula still refers to row 11 (in blue text), and I get the same exact results no matter what unique signifier I put in (see the red row for unsuccessful results).
It's strange because in the successful blue row, I simply copied and pasted from cell B11 through G11, and everything came out fine due to the relative(??) identifiers, but no luck transferring that formula to rows.
My unique identifiers seem to be off the page, but they are just 1 (starting at Old Masters) through 6 (at Impressionist).
When I type 6 (for the red row), I get the same results as for when I type 5 (for the blue row), so my unique identifiers aren't registering somehow.
I have a lookup table which needs to be able to retrieve data from columns as well as rows and not sure if this is possible.
My data is set up with an ID number, financial year and data across the columns e.g
ID F/Y Apples Pears 9999 2004/5 5 4 9999 2005/6 7 8 9881 2003/4 5 3
My lookup table will have the ID number field which will be manually entered to lookup the information on the data sheet. e.g
ID 9999 FY Apples Pears 2004/5 5 4 2005/6 7 8
I can't work out a way to get excel to only give me the number of apples and pears for that year for that ID number. Not all ID numbers will have the same number of financial years so I may need to use VBA.
I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............
i need to mark some row (which has some content written in), mark other row(with data too) and switch/transpose them mutual. when i was trying transpose method, which is using for switching rowns and columns, it wrote me error, that data are overlapping. it means it cant work on same things (rows > rows, columns > columns).
However I don't want the cell 10 rows across when a match is found I want the figure 10 rows across and then 2 rows down from that point. i.e. if the match is in row A9 I want to return the figure in J11.
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
Company 1 Contact 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Contact 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Contact 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3
I want this:
Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1 Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2 Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3
all the way down.
I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.
Column A is numbered 1 -100 successively Column B thru D contains data that goes with the assigned number in column A. I need to be able to move rows of data in column B through D to a different set of rows all at the same time (not one cell at a time) without disturbing the set numbers in column A. And with that, have all the other rows of data automatically adjust accordingly(not to be deleted or replaced).
I have individual data files (one for each participant). Each file has 64 rows (one for each trial in an experiment) and 18 columns (one for each variable). I would like to create a master data file in which each participant is represented in a single row. I will then have 1152 columns (18 columns for the variables, repeating 64 times so that each trial is represented).
In other words, my individual data files look something like this:
and I want to compile all the data into a single file that looks like this: Participant/Trial1_var1/Trial1_var2/Trial1_var3/Trial2_var1/Trial2_var2/ ... 1 55 43 65 54 43 2 . . .
I know how to record macros and use IF, THEN, AND, & SUMIF statements creatively, but that's about it. I was hoping to record a macro that I could use on each individual data file to turn it into a single row, and then paste each row into the master file.
I've used VLOOKUP based on matching one column and returning one result. No problem. Now I need to return one result based on 3 columns matching.
Assume this range of columns is named: TravelDays Origin...Location...ShipVia...TravelDays 33778...420...UPS...3 33778...425...UPS...1 33778...440...UPS...3 33778...443...UPS...3 33778...446...UPS...2 33778...447...UPS...3 33778...449...UPS...5 33778...475...UPS...3
What I'm trying to accomplish is if these columns match: i2=Lookup!TravelDays,1 E2=Lookup!TravelDays,2 N2=Lookup!TravelDays,3
I am creating a payroll spreadsheet that will look up tax deductions from a spreadsheet I pulled from the IRS website. The first two columns represent a range of values the employee's pay would fall between. The first column is "paid at least" and the second column is "but paid less than".
Once I locate which row the employee's wages fall between, I would then need to match their number of withholdings with the corresponding column. Columns 3, 4 and 5 have headings to represent 0, 1 or 2 withholdings.
The value I need returned would be the intersection of wages paid and withholdings. For my spreadsheet example, if the employee's wages are 1,023 and they have 1 withholding, I need the formula to return 147. I was able to find a vlookup/match formula but it worked only without a range of wages paid.
For example, if the employee was paid exactly 1,030 and had 1 withholding, it would vlookup/match to return 147. Obviously, this won't work with me needing to find the correct row based on where their wage falls in the ranges.
I've attached a picture of the spreadsheet example below. payroll example.jpg
I need a Vlookup to look at two columns when searching. here is an example:
Sheet 1 is my report grid to hand out to the employees, A1 has a drop-down list with my employees names and A2 one for the month, when you click an employee name the vlookup formula fills in the information from sheet 2 (data sheet).
Sheet 2 has data like: Col 1= Month, col 2=employee L-name, col 3= production etc...
currently the vlookup formula messes up because the employee name is in there multiple times because of the months column, i have to delete all months except the one i need. formula is: =vlookup(A1,'sheet2'!,B:C,2,false)
this formula will pull the data in sheet two column 3 (production) for the employee that matches.
how can i make it compare first to col 1 (month) and then col 2 (employee).
I have a list of names I need to look up against another list. My problem is that the second list has a mixed formatting of Firstname Lastname and Lastname Firstname. What I did so far is create a second column that reverses the first and last names in the column.
Table1:
A B
1 Name Name (reversed)
2 John Smith Smith John
3 Jackson Laura Laura Jackson
4 Maxwell Ted Ted Maxwell
5 Chris Hansen Hansen Chris
Table2:
A B
1 Name Currently employed?
2 John Smith =IF(ISNA(VLOOKUP([A1; Table1!A:A;1;FALSE));"NO"; "YES")
3 Laura Jackson
4 Ted Maxwell
5 Chris Hansen
The VLOOKUP formula works great but I need it to look for a matching name across Table1:A:B not just Table1:A:A. How would I do this?
I have two columns with names on it. and Column A has about 600 names and column B has about 447 names. I want to match these two columns and get the names which do not match on Column C.
To remove the error if Vlookup can't find a match - the formula above uses the ISERROR. If vlookup produces an error (can't find a match) the Excel cell returns "Not Found", otherwise, it returns a valid Amount from column 17.
This formula is inserted into an Excel specific location using Access VBA based on a set returned from a SQL Server Query.
An Excel Worksheet PositionDataBuy is created in sort order of Contract numbers.
The problem came a few days later when a condition of 3 of the same Contract numbers for the same day had different buy Amounts.
So the Vlookup found the first contract number (e.g. 70036) and the value associate with the first instance in PositionDataBuy three times)
The challange is now to use Vlookup for the contract number, then (if it matches) look over at the Amounts column and determines that from each contract record (assuming a duplicate) it also matches the volume.
So, if Vlookup's error message is False (found first value), another nested if (with the ISERROR) will check the second required column.
But, what if the valid column is on the second record with 70036? I suspect if an error was generated the Vlookup is done and it won't continue.
The data below is fudged, lets assume that no two volumes would be the same for now.
Example: 70036 also needs to be tested against volume and then return the Amount Contract Volume Amount 70035 5 100 70036 7 120 70036 3 33 70036 6 22 70037 1 11
Basically, I have an array of data (let's say A2:D20). I then want to do a VLOOKUP across the columns A:D, and return the corresponding value in column G, if VLOOKUP can find a particular value from column F, say.
So, I need a VLOOKUP function written in G2, that finds the value E2 in EITHER A2,B2,C2 or D2 (and return what is in E2, say).
I first thought I could CONCATENATE the strings in A2, B2, C2 and D2 and use some sort of VLOOKUP that finds a string within another string.
How to align two columns that sometimes have rows that are unequal in number. The data is sourced from a web page that has a table layout and the table lays out the one to many values this way in the table. My data looks like this:
The editor removes leading spaces. In the column Table there are only three rows but in column Term there are multiple rows per single row from column Table. So one row t2 for column Table has three rows in column Term. Are these cells merged and can I in a mcro detect if the cells are merged? How is the addressing of the merged cells in a VBA macro?
I have two separate worksheets, and I am trying to create a Vlookup or Index and Match formula. Here is the example:
Sheet 1 Cell A1= Employee ID: 123-D.
Sheet2 Vlookup A1 from Sheet 1, and match the first five characters to Column A, Column I and Column P. If a match, return name (e.g. John Doe) in Sheet 1, cell B1.
Not sure where to start with this one, an IF statement or vlookup? Basically I have 3 columns A, B, C (original sheet) and A, B, C in my working sheet. A contains car registrations, B contains a date and C contains a weight. I need to return C from my original sheet into my working sheet only when A and B BOTH match from my working sheet (there are scenarios where the same car registration has a different date next to it). So I want to return the weight only when the car registration and the date match in a specific instance from my working sheet.
I have a spreadsheet with twenty columns. Column A has an item number (say "Clutch"), and the remainder of the columns have values. However, there only be one column in the range B:T which will have a value on the same row as "Clutch" (say "Black" in column "N").
How I can I return "Black" using a vlookup or should I be using something else?
i have a drop down menu listing all the months (as displayed) and once I pick a month, anyone that has an anniversary OR birthday during that month loads into the columns to the right.
I have tried various ways of doing it but for some reason It just wont pull the info right. The previous posts have shown me scripts for Vlookups and a variety of other things. But the end result was not exact.
I have two columns of data each with repeats. I want to be able to look up the third column of data and then list the multiple results attaching to the first two columns.
I have attached a dummy spreadsheet of what I am trying to achieve.
I need to make a VLOOKUP in a sheet which I do not own or control the layout of. Therefore I cannot add any columns in the source to assist me in my lookup.
My VLOOKUP needs to look at two values/columns since the value in one column is not a unique identifier. This is easy in the value I want to lookup but not so easy in the lookup range in the source sheet. Ideally I would like to write my formula something like this:
i have the following table, i want my Vlookup not only get the description from Column K but to get column K , L , M City and Country as well and put them in Columns D for Description, E for City and F for Country. would this be possible with Vlookup formula or any other formula?
