Microsoft Excel forumla help!

[Jayar]

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?

Thanks in advance.

[Gazpacho]

What version of Excel are you using?

Is this what you're trying to do?

[genevieve]

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?

[Jayar]

On Tab 1:
A--------------------B
1 Business Name----Date

On Tab 2:
A--------------------B
1 Business Name----Date

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.

[mpal2]

Jayar, it sounds like you need to learn about arrays. I'm not that great with those but this is a blog site I've found to be very helpful about that kind of thing.

http://chandoo.org/wp/

Here's the archive page where you can see past topics.
http://chandoo.org/wp/archives/

If you can't find what you need, he has a section for questions.
http://chandoo.org/forums/

[Aceon6]

I think you're looking for

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

type that into the receiving cell.

[Jayar]

I think you're looking for

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

type that into the receiving cell.

Edited: OOOPS! No, it didn't work.

[talulabell]

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

[Aceon6]

Edited: OOOPS! No, it didn't work.

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.

[Jayar]

I think you're looking for

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

type that into the receiving cell.

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.

[Gazpacho]

Does this work?

[Aussie Willy]

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

[talulabell]

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.

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

[FigureSpins]

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:

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

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

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

=IF(ISNA(VLOOKUP(B2,'S1'!A:F,6,0)),"Not Found",VLOOKUP(B2,'S1'!A:F,6,0))

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

	-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	Not Found

Voila!

[talulabell]

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.

[Jayar]

WAHOO! IT WORKS! YOU ARE ALL ROCK STARS!!!! THANK YOU!!!

[FigureSpins]

Great - glad you can knock off work now! Enjoy the weekend!

[Aussie Willy]

FigureSpins - how did get a different font on your reply?

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

[FigureSpins]

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