Question about Microsoft Computers & Internet
Hi,
I need a excel formula that looks at two spread sheets and reads a generic field and finds a match then places data from another field into the other sheet.
I hope that make sense.
David
Nope, sorry, although I am truly an expert at Excel formulas, I do not understand what you are trying to end up with in the final cell. We can compare a specified field with two spreadsheets - use named ranges and index/match lookup formulas. But then where you really lose me is in reading "a generic field" to find a match, and then placing what "data from another field" into what "other sheet" - ? See the confusion?
Best way to compare 2 given parameters would be to use a nested if formula, with index/match combo. Here is a simple Excel example of how such a formula could be structured:
Sample Data (columnar arangement):
A1: Part B1: Code C1: Price D1: Find Part E1: Find Code
A2: x B2: 11 C2: 5.00 D2: y E2: 12
A3: x B3: 12 C3: 6.00 D3: y E3: 11
A4: y B4: 11 C4: 7.00 D4: x E4: 12
A5: y B5: 12 C5: 8.00 D5: x E5: 11
To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:
=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))
Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula returns the value 8.00.
To take this one step further, with range names, this example will find one value at a specified location which matches a specific row header value and column header value. Let's say the range is home values (Range=HomeVal), Column A of HomeVal contains street addresses,"row headers" (Range=StAddress), and Row 1 contains dates of the various values that are in the body of the table, "column headers" (Range=Dates). To return the specific value from the range HomeVal to another sheet, where A1=address specified and A2=date specified:
=INDEX(HomeVal,(MATCH($A$1,StAddress,0)),(MATCH($A$2,Dates,0)))
Then make sure to press CTRL+SHIFT+ENTER to enter the formula as an array formula - if you only hit enter, these types of formulas will not work properly.
Please post back if you need further help, with more details, otherwise thank you for using and rating FixYa!
Posted on Jul 08, 2008
OK, if the above won't work for you to select your data, it seems that Client & Order both contain a column titled Fund. Then you are trying to pull the Client (name) from the Client sheet into the Order sheet. Is that right?
Are all of the Funds unique per row, so that each is a one-time match? Or is the match purely dependent on the fact that the row number is the same for each where you want to pull the client name over?
If I still don't understand, how about posting the contents by row/column, and we'll work from there.
Hi there, docurley. Just thought I would check in on you to see if you got these formulas to work for you. If you still need help, please let me know, as the solution I have set forth will definitely FixYa, we just have to put it to work in the right place!
×
Hi,
a 6ya expert can help you resolve that issue over the phone in a minute or two.
best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
click here to download the app (for users in the US for now) and get all the help you need.
goodluck!
Posted on Jan 02, 2017
Jul 29, 2009 | Computers & Internet
Feb 11, 2009 | Microsoft Excel for PC
Oct 10, 2008 | Microsoft Excel for PC
Apr 25, 2008 | Microsoft Office Professional 2007 Full...
Feb 05, 2008 | Computers & Internet
Feb 03, 2008 | Microsoft Excel for PC
Dec 19, 2007 | Computers & Internet
Dec 04, 2007 | Microsoft Excel for PC
Oct 08, 2007 | Microsoft Office Standard for PC
Sep 11, 2007 | Microsoft Office Standard for PC
1,318 people viewed this question
Usually answered in minutes!
You have two spread sheets (Client & Client order) with a column named Fund on both spread sheets, so this is used to match the two lines.
Once this you match that I need it to pull the data from column B in the Client field (this is a different column but the same row as to what the two fields match) and drop the data in to a new field in Client Order work sheet but on the same row that matches the Fund code.
Hope this is clearer.
×