Mansfield
|
| posted on 1/9/06 at 04:21 PM |
|
|
Are there any Excel experts out there?
I need to the cells in column B to be populated with the information in column E if the cells in column A match with the cells in column D.
I have found the MATCH command, column C shows where A and D match, but the result is not what I am after. And I am now stuck. I am expecting to use
an IF command somewhere, but I cant find how.
Can anyone help?
David.
[Edited on 1/9/06 by Mansfield]
 
Rescued attachment match.jpg
|
|
|
|
|
Aboardman
|
| posted on 1/9/06 at 04:34 PM |
|
|
is it this simple
=IF(A1=D1,E1,0)
copy that down the column B
if not let me know what you are looking for, i will be able to come up with something.
|
|
|
mark chandler
|
| posted on 1/9/06 at 04:37 PM |
|
|
You need VLOOKUP
the sum in column b1 is:
=VLOOKUP(A1,D:E,2,FALSE)
Then drag down the rows
[Edited on 1/9/06 by mark chandler]
 
Rescued attachment excel.JPG
|
|
|
StevieB
|
| posted on 1/9/06 at 04:37 PM |
|
|
Might be more suitable to an IF formula, but that's because I've never been able use the LOOKUP functions properly (if I did, my job would
sooooooooooooo much easier and my people could be the best at their jobs rather than just being really good!)
|
|
|
Tangerine Scream
|
| posted on 1/9/06 at 04:41 PM |
|
|
I'd go for
=IF(A2=D2,E2,"")
in B2
V & HLOOKUP scare me !!
HTH
Steve
|
|
|
mark chandler
|
| posted on 1/9/06 at 04:42 PM |
|
|
Vlookup is king, you can also look for text in text by using wildcards, if you want to find names with "the" in them from you column you
could search for "*"&the&"*" and it would return katherine if that was the sought text.
Worth mastering as it works between worksheets and workbooks, even closed ones.
Regards Mark
[Edited on 1/9/06 by mark chandler]
|
|
|
Mansfield
|
| posted on 1/9/06 at 04:43 PM |
|
|
I have a sheet with 15000 rows that I need to treat like this. This is my practice sheet.
So far I have
=VLOOKUP(A2,D2:E11,5,FALSE)
That has not worked yet, but it may be close.
|
|
|
StevieB
|
| posted on 1/9/06 at 04:44 PM |
|
|
Here's a good one for LOOKUP then - I need a spreadsheet to search by name and produce the persons grade and cost rate - how the hell do I do
that?
I've been trying for 2 years on and off and always end up with an error - it's the Holy Grail of Quantity Surveying! (that, and Pivot
Tables, anyway!)
|
|
|
Mansfield
|
| posted on 1/9/06 at 04:46 PM |
|
|
Genius, Mark. Thank you.
 
Rescued attachment lookup.jpg
|
|
|
Humbug
|
| posted on 1/9/06 at 04:59 PM |
|
|
If you do use the VLOOKUP, I would avoid:
=VLOOKUP(A2,D2:E11,5,FALSE) because when you copy it down, the next row will be =VLOOKUP(A3,D3:E12,5,FALSE) and so on, i.e. it will not
lookup up the same table. Also, putting 5 will not look at anything - the number refers to the nth coluimn in the lookup range. It should be 2.
=VLOOKUP(A1,D:E,2,FALSE) because this is looking up the whole of columns D and E unnecessarily - OK in a small workbooks, but in a big one it could
slow things down.
I would recommend either:
=VLOOKUP(A2,$D$2:$E$11,2,FALSE) - the $ signs force it always to keep the cells referecnes even when the formula is copied
or name the range D2:E11 as something (e.g. Insert->Name->Define Lookup_Table), then put =VLOOKUP(A2,Lookup_Table,2,FALSE) where
"Lookup_Table" is the name you decided.
|
|
|
Aboardman
|
| posted on 1/9/06 at 05:37 PM |
|
|
quote:
Originally posted by StevieB
Here's a good one for LOOKUP then - I need a spreadsheet to search by name and produce the persons grade and cost rate - how the hell do I do
that?
I've been trying for 2 years on and off and always end up with an error - it's the Holy Grail of Quantity Surveying! (that, and Pivot
Tables, anyway!)
how are the name done? full, inital and surname, let me know should be able to crack that one.
|
|
|
StevieB
|
| posted on 1/9/06 at 06:18 PM |
|
|
There's several ways to run the report, but full name is the usual way we run the reports - any pointers would be greatly appreciated!
|
|
|
StevieB
|
| posted on 1/9/06 at 06:22 PM |
|
|
Just got the email - your a star!
|
|
|
