Lookup 2 Columns And Match To 2 Other Columns

Feb 15, 2007

I am trying to do a (what I thought was a) simple lookup involving 2 columns.
Pls see attached example. I am trying to find the "?" values, by matching column A&B of the input to the Data columns and returning the $$$ column.

I thought this wouldve been a piece of cake but its got me stumped!

View 9 Replies


ADVERTISEMENT

Lookup When Two Columns Match

Feb 19, 2007

how does the formula change when i want a new column to "bring in" ( lookup?) a corresponding value from the second sheet when it sees that the names match?

For instance:
sheet1
FIRST LAST HEIGHT1
John Smith 5'9

sheet2
FIRST LAST HEIGHT2
John Smith 5'11

So, the conditional format would be a 4th column in the first sheet-- still comparing the first & last names-- but when they match it enters "HEIGHT2" into this 4th column.

This is still a conditional format, right? sorry if it doesn't belong here

View 9 Replies View Related

Lookup When 2 Columns Match

Jan 1, 2008

i did find a thread concerning my question but the topic was closed & the OPs best answer had been edited & removed for some reason...

i'm comparing annual pro sports stats for a project & want to do a vlookup contingent on pairs of columns matching between two sheets. ie if a first AND last name in one sheet match a first and last name in a second sheet then fill in x statistic. i dont know VB or anything, but i assume this can be done w/ a pretty simple formula

attached small example

View 3 Replies View Related

Lookup/Match/Sum - Several Worksheets And Different Columns

Apr 27, 2009

I'm working on budgets (salaries). I have one main spreadsheet with all employee names listed - other spreadsheets are for each department. I need to bring the total salary from the department spreadsheets and would like to do this by looking up (or matching?) the names in the column from the main sheet to the department sheets and then putting the salary from the department spreadsheet to the main spreadsheet. In addition, I have several that have their salary split between two or more departments. On my department spreadsheet I have a column labeled % of salary for this department. On the main spreadsheet, I want to figure out a formula that will sum the percentages for a certain person (so I make sure I don't go over 100%).

For example - Joe Smith's name is on the main employee spreadsheet. He is also listed on 3 other department spreadsheets, with % of salary listed as 30% for department 1, 30% for department 2, and 40% for department 3. On the main spreadsheet I need to add up Joe's percentages from the department spreadsheets.

View 6 Replies View Related

Lookup Multiple Columns Match

Feb 9, 2012

I have data like this:

Code:

500a
TRUE470b
440c
TRUE410d
TRUE380e
350f

[Code]...

and I want to look up an approximate value in Col2, say 310, and return it's next-highest friend from Col3, in this case "g". This is easy enough with INDEX and MATCH. But I want to take it one step further and only use those values which are approved by Col1 - so in this case I want 310 to ACTUALLY return "e"

I found some good information here which gets me close. The following works very well. It uses a boolean & operator to match two values at once, but it only works for exact matches. This example goes down the list and finds the first "sydney" which has an "x" and gives the result "h".

Code:

xmelbournea
sydneyb
xadelaidec
xmelbourned
sydneye

[Code]....

When I use this approach on data like in my first example it falls over, my guess is because the boolean & falls down before the MATCH function has a chance to accept an approximate match.

View 5 Replies View Related

Lookup And Match 2 Values Within 2 Columns

Apr 16, 2014

I want to search (sheet2 column A and B )for a match of (cell A2 and B1 of sheet2) and return value into sheet2 cell B2 Completed and if not found Outstanding

sheet1
A john
B Week 1

sheet2
A2 john
B1 Week 1

sheet2
B2 Completed

Here is a formula I had been using in a google spreadsheet but does not work in excel

=Iferror(If(Filter('DATABASE'!$C:$D;'DATABASE'!$C:$C=SPREADSHEET!$A2;'DATABASE'!$D:$D=SPREADSHEET!G$1)>1;"Completed";"Outstanding");"Outstanding")

View 1 Replies View Related

Lookup Between 2 Columns, Match Next Greater Value

Aug 4, 2008

Please see the attached example of my problem. I have TIME in column A in minutes and % in Column B. What I would like to do is search the % column against the value inputed into E2 (in the example 52%) and find the corresponding TIME value (Col A). However, where there is no exact match of the E2 value I would like to select the next greater value. For example as there is no 55% in my example the formula would move on to 57.27% and record 21 in D2.

View 9 Replies View Related

Match Multiple Columns To Lookup Value

Feb 20, 2008

I'm using an index array formula I learned here to lookup a value between 2 sheets in a workbook. I have had no trouble using it when matching just 2 pieces of information (first & last name), but now I need to make 3+ matches to get the value & the formula is no longer working.

When I check "show calculation steps" in the attached example, I can actually see that all 4 items have successfully matched across the 2 sheets but for some reason the formula is still not pulling in the desired value. Am I wrong to think that I can match more than 2 items w/ this kind of a formula?

View 3 Replies View Related

Lookup & Cross Match 2 Columns In 2 Workbooks

Aug 23, 2008

I have 2 reports with the same column headings. I want Excel to compare each one on the common OrderID and then just show me the ones that don't match--either one invoice can't be found on the other, or in the cases where they are found, the invoice amounts do not match (IOW create an exception report).

View 2 Replies View Related

Match And Index Function For Lookup (Left Columns)

Jul 12, 2014

I need a formula for left coulmn lookup (using index and matching function) and lookup array has duplicate value.

View 1 Replies View Related

Index / Match Lookup With Two Columns - Runtime Type 13 Error

Feb 14, 2014

I am building a timeclock, and am using a user form to allow people to sign in/out.

I have a punch in form that logs the inputed username and password (after being checked against my employee list), along with a count of their username on this sheet (as a count of how many times they have punched in) and a timestamp (column 4). These values are all pasted to the first blank row in my worksheet "PunchInTimes".

Then, I have a punch out button that logs these same values the same way, only on "PunchOutTimes". Then, the idea is to link the index number of the PunchOutTimes record and PunchInTimes record for any given user, so that when they punch out, a complete record is pasted (to the first blank row) in a separate worksheet called "Timesheets".

I have been able to paste the username and count to the "Timesheets" sheet, however as soon as I try to index/match the count and inputed username (on the punch out form), I get an error 13 type mismatch.

I'm trying to paste the corresponding punch in time to this punch out time, so ive used an index/match with two columns (username and count) found in "PunchInTimes", and try to return the corresponding 4th column value (punch in time). After pasting the "punch out only" values to the PunchOutTimes, i used a "With/End With" statement, and set wsc equal to my 'timesheets' worksheet at the beginning of my code. So the line where I get my error is below in bold:

[Code] ......

View 4 Replies View Related

Subtracting 2 Columns Of Data From Each Other When Labels In Separate Columns Match

Feb 20, 2009

I have four columns of data, as follows:

label 1, value 1, label 2, value 2

I need to create a formula in the fith column that for each line will tell excel to:

look for entry in 'label 1' in 'label 2' if there is a match, then subtract value 1 from value 2, display result.

I have tried doing this with SUMIF but am getting nowhere fast....

View 7 Replies View Related

Lookup Date In 4 Columns And Get Reults In One Columns

Jul 9, 2009

i'd like when i lookup abt specific in range (4 Columns is my lookup range)
get the results in one column with skipping blanks Cells "Note" Blanks cells it contains fromulas with "" so it blanks but have formulas ....

View 9 Replies View Related

Copy Columns Between Worksheets If The Columns Don't Match

Jun 7, 2006

I have 2 worksheets in which I have to copy one column of cells from one to
the other. The problem is that the "main" worksheet lists nearly 3,500
clients and I have to copy numbers into the "main" worksheet on weekly basis
from another worksheet that lists only about 1,100 of those 3,500 clients.

Both client lists are in alphabetical order, but I obviously cannot just copy
one column into the other, because then the numbers won't be matching the
correct clients.

View 10 Replies View Related

How Do You Sort Columns So That They Match Up With Other Columns With Like Data

Jul 2, 2008

I have two columns one is web addresses and the other is email addresses but the rows do not line up. I was hoping that since the second half of the email address matches the web address I could somehow sort them so that the email address column and web address column match up. Here is an example but keep in mind that this list is about 9k long and this is just a sampling so you may not see any in this example that match. Also I may have more than one email address per website.

View 9 Replies View Related

Match 2 Columns To 2 Seperate Worksheet Columns

Sep 11, 2006

Now i have one excel sheet with two separet sheets in it ( Sheet 1 ) and ( Sheet 2 ) . I have in sheet 1 a column A with material codes and ColB is discreption and Col C IS Prices . But in Sheet 2 Col D is materila Code and Col I is a price .

the recordes in sheet 1 are around 11000 but in sheet 2 are around 2200 where the sheet 2 has a specific materials from sheet 1 .

Now i want to update the prices in sheet 2 from sheet 1 for each item after confirm that the materila code in sheet 2 equal in sheet 1 so copy the price from sheet 1 to sheet 2

Sheet 2 is old prices and sheet 1 has new prices so i need update the new prices in sheet 2

View 9 Replies View Related

Match 2 Columns To Copy Information In Other Columns

Apr 21, 2007

I have 2 workbooks (workbook1 & workbook2). Information in column A and column N must match both workbooks per row/ cell (ie. if A2 and N2 in workbook1 match workbook2, copy information from 3 other columns (S, T, U from from workbook1 into workbook2 - cells S2, T2, U2). Workbook2 may have information in column S, T, U, but I need to over-write it with the information from workbook1. If the information does not match per that row/cell (ie. A2 in both worksheets are the same, but N2, they are not, so go to the next line that does match and copy the information into worksheet2). I do not want to use a V- lookup for this problem. I really need a marco.

View 3 Replies View Related

Multiple Lookup Values Rows And Columns To Lookup Single Target Column On Right End?

Apr 7, 2014

I have a table of data (say Column1 to Column 5) with multiple rows.

Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.

I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.

It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.

View 3 Replies View Related

Match 1st And 2nd Columns In 2 Sheets And Return 3rd / 4th And 5th Column If Match

Dec 15, 2013

[URL]

What im trying to do is match columns A & B from AUDIT Sheet to Columns A & B in MASTER sheet. If they match then pull columns C, D & E from MASTER into AUDIT.

View 9 Replies View Related

Lookup Two Columns With Two More Columns?

Feb 11, 2014

I m trying to figure out a formula to lookup Two columns(New Data) with Two more columns(Old Data).

Need to lookup values i.e. is Receipt No with Amount in the New data with the Receipt No & Amount in the old data"

Old Data
New Data
Receipt
Amt

[Code]....

View 6 Replies View Related

How To Match 2 Columns And Count Their Match

Jan 28, 2008

HOw can I match combination columns of A-F (row1) to combination H-M (rows1 to end) and show how

many are number matched in column N..

ex..
Rows

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

2

3

16

17

11

9

11

17

18

View 9 Replies View Related

Lookup In Two Columns?

May 16, 2014

I'm trying to come up with a lookup formula that would look at Column A and Column B then return the matching result from column C. If I entered A in a cell it would return 11, but if I entered 1 it would still give me 11. how to do a lookup with two columns, but didn't get far.

lookup.JPG

View 4 Replies View Related

Three Way Lookup (1 Row / Two Columns)

Mar 16, 2014

Here is my requirement...

Oct-13
Nov-13
Dec-13

Item name
Cost
MRP
Qty
Cost
MRP
Qty
Cost
MRP
Qty

[Code] .....

I need to find the MRP value of stone clip for nov-13.

View 9 Replies View Related

Match Columns ...

Apr 9, 2009

I need to match 2 columns, example:

PHP
A          B         C
111       111       111
111       222
222       333       222  
333       666       333
333       666 
555
666                 666
666                 666 

Ok here is the example of 2 sorted columns A and B, the result in C column should look than like i posted. C is the matched B column with A.

I heared there is a vlookup function for that, but I don't know how to use it :S

View 10 Replies View Related

Match The Other Columns

Jan 14, 2008

What do I do to setup a column so that is matches the other columns. Basically when the other columns move, this column will move with it.

Example:-

Name Team

John Smith Old Mill
Terry White Pickwick
Martin Jones The Star

How do I get it so that when the player column moves, the team column moves accordingly?

View 9 Replies View Related

Lookup Function For Two Columns And One Row?

Aug 1, 2014

I have read through other similar topics and tried using the index and match function to lookup but been unable to make the formula work.

I am trying to use the lookup function to pull data from a table using criteria in two columns and one row. I have attached a spreadsheet for example.

I want to use criteria of Day (eg . 1), Total height (cm), and jar # (eg. 1). I inherited this table from a previous coworker and don't have time to organize the data better.

In the spreadsheet I want to return the value of 8.8 (highlighted in yellow). The criteria I want to use to lookup the value are highlighted in red.

View 4 Replies View Related

Lookup And Count On Two Columns

Apr 1, 2009

Im trying to return the number value of how many contracts have been signed in a particular month. So in my example I want to look pick out say "IM" and check to see how many of those types have been signed in say "Jan-09" and return that as a number. Have tried various mixes of count, sum, countif and sumif but dont seem to be getting anywhere.

View 4 Replies View Related

Lookup In Multiple Columns

Aug 12, 2009

I have been already fighting for about a week with this problem. I attached the Excel-file. I have three input data and as output I want to get also three different data. For Vendor I use the VLOOKUP function. For agreement# I have used (not in this file) CONCATENATE by adding an extra column to the data table and then use VLOOKUP.

The actual problem is to find the price because it is based on the three input data. I can't seem to find a function for it. I've tried INDEX, MATCH, IF, nested them for as far as my knowledge reaches.
The problem lies for as far as I know in the del.date cell. For example, input date is:
Client#:912345, Product:E569831, del.date: 16.7. (July 16th). This input date should return cell G12.

View 3 Replies View Related

Lookup Function For Three Columns

Nov 16, 2012

A
B
C

Ap1
queens Carrots
Goja Apple

Ap2
fujja Apple
fujja Apple

Ca23
Goja Apple
queens Carrots

I tried to write a function so that it would search for a word from Column C in Column B, and then when it found it, give me a a combined result of Column A and B. For instance. I lookup up C1 (Goja Apple) in the range of all of column B; the function finds it in B3 (Goja Apple); then it returns the combined result A3 and B3 (Ca23 Goja Apple). I tried the =lookup, but I think the first column you select must be in ascending order, and that wont work for me.

View 3 Replies View Related

Lookup Many Rows And Columns

Dec 29, 2002

My daily receiving list has Item# received that day e.g.
ReceiptList.xls
Item#
123
456
I need to compare this to a Data Base list that has 10000 item# , seller, and status (this data base can have repeated same item# but varied seller and therefore different status
e.g.
Ing.xls (Data Base file)
Item# Seller Status
123 ABC Approved
123 GMC Not Approved
456 ABC Pending
456 XYZ Not Approved
456 QRS Approved
etc. +10000 items
Now I need to compare the daily receipts to the data base file and pull all the sellers for those particular items and their status which like I said could be on many rows, one row, or not listed at all to the ReceiptList.xls(which I can then make sure that the received items are from an Approved source etc.)

View 13 Replies View Related







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