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

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.

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

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

I'd go for

=IF(A2=D2,E2,"")

in B2

V & HLOOKUP scare me !!

HTH
Steve

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]

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.

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

Genius, Mark. Thank you.
Rescued attachment lookup.jpg

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.

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

---

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!

Just got the email - your a star!