VLOOKUP Across Rows And Columns

Apr 10, 2012

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.

Item Code

Dept
Description
Qty.
Price
In stock
Line Total

1
Old Masters
Painting
100
5

2
New Masters
Sculpture
200
10

[Code] ........

View 9 Replies


ADVERTISEMENT

Vlookup Table To Lookup Data In Columns And Rows

Apr 28, 2007

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.

View 7 Replies View Related

Multiple Ccolums/rows To Get Data From Multiple Columns/rows (vlookup)

Jan 15, 2010

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:..............

View 3 Replies View Related

Transpose (switch) Rows Into Rows And Columns Into Columns?

Jul 28, 2013

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).

View 14 Replies View Related

Vlookup And Offset: The Figure Of 10 Rows Across And Then 2 Rows Down From That Point

Jun 22, 2007

I have done a search and I think this is possible but just can't convert any of the examples to work for me.

This is the calcuilation I would normally enter -

=VLOOKUP(A80,'[CF227.xls]Sheet1'!$A$9:$J$9,10,FALSE)

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.

View 4 Replies View Related

Moving Data From Rows To Columns And Delete Repeated Rows

Apr 16, 2014

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.

View 5 Replies View Related

VLookup In 2 Columns

Aug 20, 2014

I'm looking for a vlookup that will look in 2 columns of the array table and output an answer based on them.

See the attached example : Example.xlsx

View 5 Replies View Related

VLookup - How To Sum Two Different Columns

Jan 13, 2014

I have the following concern as i'm not able to solve it alone:

Ex: =vlookup(c11;flyingprogram;2+5;false)

View 4 Replies View Related

VLookup In Different Columns

Feb 21, 2013

Code:

A B C D
1 2801 2 2816 1
2 2802 2 2815 0
3 2803 3 2814 88
4 2804 4 2813 99
5 2805 5 2812 2
6 2806 6 2811 6
7 2807 4 2810 1
8 2808 3 2809 4

I'm new here and I saw that many people actually have excellent knowledge in excel.

Well, I'm trying to use vlookup on my sheet, but the data I have comes with the code and your score his side.

In a standard vlookup would be: = vlookup (value, range, 2, false).

This is the problem, I would like that when he could not find the value in column A or B, were to column C or D and so on.

View 9 Replies View Related

Vlookup Two Columns

Jul 3, 2009

I have a spreadsheet with the following data:

A B C D E
18500 Eggs 4 6 9
18500 Bread 2 0 1
18500 Cheese 3 8 4
71200 Eggs 9 6 3
71200 Bread 2 8 4
71200 Milk 1 0 8

I need a formula to check column A, then B and return a result from C:E.

Column A is 18500 or 71200
Column B is Eggs, Bread, Milk or Cheese
Column C, D E are sales figures.

View 9 Replies View Related

Getting Rows To Columns Macro Skipping Blank Rows

Dec 28, 2011

I have this in Column A, with about 120 entries:

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.

View 5 Replies View Related

Move Rows Of Data From Several Columns But Not All Columns?

Dec 18, 2013

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).

View 1 Replies View Related

Turning 64 Rows And 18 Columns Into One Row With 1152 Columns

Aug 10, 2008

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:

Participant 1 (title of data file)

Trial/var1/var2/var3
1 55 43 65
2 54 43 56
3 33 23 56

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.

View 9 Replies View Related

VLOOKUP On Multiple Columns?

Jun 10, 2014

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

My result should be: Lookup!TravelDays,4

View 14 Replies View Related

VLookup Using Two Values In Two Columns

Aug 11, 2014

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

View 2 Replies View Related

Vlookup - Compare Two Columns

Oct 26, 2009

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).

View 10 Replies View Related

VLOOKUP Across Multiple Columns?

Dec 16, 2012

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?

View 2 Replies View Related

Vlookup Match The Columns

Sep 23, 2008

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.

View 11 Replies View Related

Vlookup Test Against 2 Columns

May 21, 2009

=IF(ISERROR(VLOOKUP(70036,PositionDataBuy!$D$2:$U$505,17,FALSE)),"Not Found", VLOOKUP(70036,PositionDataBuy!$D$2:$U$505,17,FALSE))

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

View 13 Replies View Related

How To Get VLOOKUP Over Multiple Columns

Jul 20, 2011

i am trying to get a VLOOKUP to work over a number of different columns. An example the problem:

Sheet1

A B

1 10
2 20
3 30
4 40
5 50

Sheet1 is the sheet i want to lookup the values and i want to place the results into column B

Sheet2

A B C
1 10 40 50
2 20
3 30

Sheet2 contains all the infromation that i want to look for to make the comparison.

I know that the expression used for the VLOOKUP is:

Code:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The issue i have is "col_index_num" as i want to look over multipule columns. I have been using this expression:

Code:
=VLOOKUP(A2,Sheet2!$A$1:$J$20000,1,FALSE)

How do i change the "col_index_num" to look over all 3 columns and not just the 1st, 2nd, or 3rd?

View 5 Replies View Related

VLOOKUP Across Multiple Columns?

Aug 19, 2013

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.

View 2 Replies View Related

Aligning Rows Of Different Columns With Unequal Rows?

Feb 11, 2013

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:

Table ...Term
t1 ..... term1
t2 ......term2
..........term2.a
..........term2.b
..........term2.c
t3 ......term3
..........term3.b

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?

View 2 Replies View Related

Vlookup To Return One Value From Multiple Columns?

Sep 21, 2013

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.

View 9 Replies View Related

Match To Columns To Each Other Before Returning VLookup

Apr 10, 2014

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.

View 6 Replies View Related

Vlookup From Multiple Columns With Only One Return

Dec 19, 2008

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?

View 4 Replies View Related

Vlookup: Month Loads Into The Columns To The Right

Jan 24, 2009

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.

View 3 Replies View Related

VLookup In Different Sheets Comparing Columns

Jun 25, 2014

Is it possible to use multiple Vlookups in one macro?

Currently running a vlookup of computer names between multiple sheets, i need to be able to match product name and computer name between sheets.

For example (Sheet 2 column B) compare it to (sheet1 column B and C)
if it gets a match then vlookup to (sheet 2 column A) to (sheet 1 column A)

Spreadsheet attached : excelhelp.xls‎

View 2 Replies View Related

Vlookup Multiple Items Using Two Columns

Sep 14, 2009

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.

View 9 Replies View Related

VLOOKUP Where LOOKUP Range Is Two Columns?

Oct 27, 2011

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:

=VLOOKUP(A2&B2;CONCATENATE('[OtherWorkbook.xlsx]Sheet1'!$M$2:$M$9;'[OtherWorkbook.xlsx]Sheet1'!$N$2:$N$9);2;FALSE)

View 9 Replies View Related

VLOOKUP Selecting Multiple Columns?

Sep 11, 2012

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?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved