# Microsoft Excel forumla help!

Discussion in 'Off The Beaten Track' started by Jayar, Mar 4, 2011.

1. ### JayarWell-Known Member

8,916
2,709
113
Hi everyone,

I am stuck with a formula, and I figured I would check here to see if there are any experts...

I need to feed information from one sheet to another based on a variable. Here goes:

If Page 2's Cell A2 equals Page 1's F2, then the cell in question will equal Page 2's cell F2

This will be repeatable for hundreds of rows. Basically, I need to say that if a certain cell is a certain customer name, then this cell is a certain ship date.

Make sense?

2. ### GazpachoWell-Known Member

5,658
1,129
113
What version of Excel are you using?

Is this what you're trying to do?

3. ### genevievedrinky typo pbp, closet huggerStaff Member

33,633
11,902
113
It sounds ilke you might want to use the vLOOKUP (or hLOOKUP) formula - it searches for specific data out of a table array and plugs in an answer based on what is found. Is that what you're trying to do?

4. ### JayarWell-Known Member

8,916
2,709
113
On Tab 1:
A--------------------B

On Tab 2:
A--------------------B

I need a formula that picks up B1 on Tab 2 if Tab 2 A1 is equal to Tab 1 Tab A

I know that this sounds like a lot of work for something that I could just copy and paste, but I have all of the customers listed on Tab 1 with TONS of other information. Tab 2 will grow as we pick up more and more materials. Rather than having to match everything up, I want to be able to automate it. Does this make sense?

I am using Excel 2003 for PC.

9,304
1,558
113
6. ### Aceon6Hit ball, find ball, hit it again.

9,678
4,993
113
I think you're looking for

=IF(Sheet1!A2=Sheet2!F2,Sheet2!F2,"no match")

type that into the receiving cell.

Last edited: Mar 4, 2011
Jayar and (deleted member) like this.
7. ### JayarWell-Known Member

8,916
2,709
113
Edited: OOOPS! No, it didn't work.

Last edited: Mar 4, 2011
8. ### talulabellWell-Known Member

1,701
114
63
Lets see if this is what you're looking for (rw is the row number, a and b are column headings):

sheet1
rw.....a....b
1......x...dog
2......y...cat
3......z...bird

sheet2
rw.....a....b
1......x...blue
2......x...green
3......z...yellow

in column c of sheet2, I put the following function:
=IF(A1=Sheet1!A1,B1," ")
(if A1 equals sheet1,A1, then use B1, else leave blank)

Sheet2 ends up looking like this:

sheet2
rw.....a....b.......c
1......x...blue.....blue
2......x...green... <blank>
3......z...yellow..yellow

Is that what you're looking for? did that even make sense?

ETA... Damn... in the time it took me to type this up, you already got the answer (and much more concisely, LOL).

Jayar and (deleted member) like this.
9. ### Aceon6Hit ball, find ball, hit it again.

9,678
4,993
113
Hmmm. Not sure why. I'll dig out the old Excel book and see if there's something I forgot.

You may need to play around with the \$ symbol. For example, \$Sheet1! means that the reference is always on Sheet 1. Don't do that with the cell, though, the expression \$A\$2 won't work as it won't change the formula when you do your copies.

10. ### JayarWell-Known Member

8,916
2,709
113
It pulls a value from Sheet 2, but it doesn't pull the correct one. If Sheet1 A2 is Disney and Sheet2 A2 is Disney, then I need it to pick up the date in Sheet2 F2. However, Disney won't necessary be in Sheet2 A2. It could be in Sheet2 A1222. I need the formula to only grab the date when it matches the company name... and I have hundreds of companies to do.

11. ### GazpachoWell-Known Member

5,658
1,129
113
Does this work?

12. ### Aussie WillyHates both vegemite and peanut butter

19,413
4,237
113
I can't really help you there. Working out forumulas can be a real challenge. But once they work it is so satisfying. Particularly if it solves a big problem (which I have had to do a work recently).

13. ### talulabellWell-Known Member

1,701
114
63
So... is this saying that you have Disney in sheet 1 A1, and you know it will be somewhere in sheet 2, column A, but you dont know where. You need to find it on sheet 2, column A, and when found, you need the value in that same row, column F? Something like that?

something like...
where sheet1.a1 = sheet2.aN then receiving cell = sheet2.fN (where N is the row it was found in)?

Hmmmm....

14. ### FigureSpinsNew Member

2,062
200
0
You need to use a VLOOKUP function. Let me play with it - I have the same version of Excel. (I love Excel problems. Anytime someone's stuck on an Excel problem, they call me and ask for "The Excellent Excel Help Desk" Except DH, who's really annoying in describing his problem with too many words. I'm working here!)

Here's my working example:

PART 1:
The tab "Sheet 2" contains the values to be "found" by the lookup function:

Code:
```	-A-	-B-		-C-	-D-	-E-	-F-
1	BusName	Street1		City	State	Zip	Date
2	ABC   	123 Mickey Street	Anywhere	FS	12345	03/20/11
3	APPLE 	1 Apple Way	Anywhere	FS	12345	03/09/11
4	DISNEY	246 Duckie Drive	Anywhere	FS	12345	03/12/11
5	IBM   	One IBM Court	Anywhere	FS	12345	03/01/11	```
This is called an "Array" when you read the Help info.

The Lookup function will only work properly IF this list contains one and only one row for each BusName value.
Otherwise, you have to change the "Range Lookup" parameter. If you need to do this, let me know and I'll write it up for you.

Notice that the data is in order by the BusName in Column A. This is important because the lookup functions require the array to be in order by that "key" column value. If it's not in order, sort it using <alt><data><sort> and keep it in that order going forward or your lookups will go awry when you add a row that is out of sequence.

I could refer to this range as 'S1'!A1:F5 in the vlookup formula, but since the list may grow and you don't want to recode the formulas, it's better to refer to it as 'S1'!A:F and save yourself a headache later when you forget about this step.

The Absolute Reference talulabell suggests works, but you'll have to update all the formulas if rows are added or deleted from the lookup array.

You can also "name" the range, but you'd also have to adjust the rows if you add/remove things from this list.

Use these approaches if they make more sense to you, but if you start getting errors in your formulas or invalid "not founds", check your formula's lookup range or replace it with 'S1'!A:B.

PART 2:
The tab "S2" contains the order information, where the customer name can appear more than once, looks like this:
Code:
```	-A-	-B-	-C-
1	ORDER#	BusName	Date
2	101	DISNEY
3	102	ABC
4	103	IBM
5	104	APPLE
6	105	DISNEY
7	106	ABC
8	107	IBM
9	108	APPLE
10	109	DISNEY
11	110	ABC
12	111	IBM
13	112	APPLE
14	113	DISNEY
15	114	ABC
16	115	IBM
17	116	APPLE
18	117	NBC   		```
If I type this formula into Cell C2:
=VLOOKUP(B2,'S1'!A:F,6,0)

...and copy it down the column to the other cells, I end up with this:

Code:
```	-A-	-B-	-C-
1	ORDER#	BusName	Date
2	101	DISNEY	3/12/2011
3	102	ABC	3/20/2011
4	103	IBM	3/1/2011
5	104	APPLE	3/9/2011
6	105	DISNEY	3/12/2011
7	106	ABC	3/20/2011
8	107	IBM	3/1/2011
9	108	APPLE	3/9/2011
10	109	DISNEY	3/12/2011
11	110	ABC	3/20/2011
12	111	IBM	3/1/2011
13	112	APPLE	3/9/2011
14	113	DISNEY	3/12/2011
15	114	ABC	3/20/2011
16	115	IBM	3/1/2011
17	116	APPLE	3/9/2011
18	117	NBC	#N/A```
Notice the "N/A" in the last entry? That's because NBC isn't on the other list to be found. Let's modify the formula to test for that error and show "Not Found" instead.

PART 3

Type this formula into Cell C2:

...and copy it to the other cells, like so:

Code:
```	-A-	-B-	-C-
1	ORDER#	BusName	Date
2	101	DISNEY	3/12/2011
3	102	ABC	3/20/2011
4	103	IBM	3/1/2011
5	104	APPLE	3/9/2011
6	105	DISNEY	3/12/2011
7	106	ABC	3/20/2011
8	107	IBM	3/1/2011
9	108	APPLE	3/9/2011
10	109	DISNEY	3/12/2011
11	110	ABC	3/20/2011
12	111	IBM	3/1/2011
13	112	APPLE	3/9/2011
14	113	DISNEY	3/12/2011
15	114	ABC	3/20/2011
16	115	IBM	3/1/2011
17	116	APPLE	3/9/2011
Voila!

Last edited: Mar 5, 2011
Gazpacho, Aussie Willy, Jayar and 3 others like this.
15. ### talulabellWell-Known Member

1,701
114
63
Yes! vlookup. It's something like this:

=VLOOKUP(A1,Sheet2!\$A\$1:\$B\$22,2)

I did this on sheet 1, and my receiving cell was also on sheet 1, so if there is no "Sheet!" indicator, it's because that cell is also sheet 1.

A1 is the first cell it's looking up. Disney, in sheet 1, A1. this cell reference is relative, meaning, when you copy this for subsequent rows, it will change for each row (A1, A2, A3, etc).

Sheet2!\$A\$1:\$B\$22 references the 2nd worksheet, cells A1 thru B22 (I put the company name in column A, random dates in B). These cell references are absolute. meaning, when you copy this formula down several rows, it should always stay A1..B22 (the dollar signs make it absolute). You dont want the row numbers increasing for each row you copy, or your range will be off.

Forgot the third part of the statement:
the final 2 is the column of the above range that holds the data you want. In my case it was the second column where I had the data. This is relative to what you select as your range. you count the number of columns in your range (not from column A, if that's not part of your range).

Final edit:
I had some issues with this working on later rows, and discovered it was because my first column of that range was not sorted. My A1-B22 range. The range you reference must (I think) be sorted, ascending, by the first column.

Last edited: Mar 4, 2011
16. ### JayarWell-Known Member

8,916
2,709
113
WAHOO! IT WORKS! YOU ARE ALL ROCK STARS!!!! THANK YOU!!!

Last edited: Mar 5, 2011
Gazpacho and (deleted member) like this.
17. ### FigureSpinsNew Member

2,062
200
0
Great - glad you can knock off work now! Enjoy the weekend!

Last edited: Mar 5, 2011
18. ### Aussie WillyHates both vegemite and peanut butter

19,413
4,237
113

I will call upon you next time I need Excel help.

19. ### FigureSpinsNew Member

2,062
200
0
I used the font menu on the Advanced post editor. I think it's the "Fixedsys" font.
(My User Control Panel / Options are set for the "Enhanced Interface - full WYSIWIG editor.")

You can also type the font tags:

[ font=Fixedsys ]This test is in Fixedsys font[ /font ] yields this : This test is in Fixedsys font

The little boxes are created using the [ code ] [ /code ] tags.

(Take out the extra spaces between the brackets.)

Last edited: Mar 5, 2011