Formula assistance please in Excel 2002

  • Thread starter Thread starter RD
  • Start date Start date
R

RD

OK, here goes:

I have a sheet with values in column a, b, c and d, call the sheet
"Projects"

I have another sheet, call it, "Time", where in A1 I put a number or value
which I want it to look up in Column 'a' in Projects. When that value is
found it returns to "Time B1" the value in "Projects column b adjacent to
the value in 'a'".
It also returns to "Time C1" the value in "Projects column c adjacent to the
value in 'b' and concatenates it with the value in column d"

Hope that makes sense! Thanks in advance.

Example ( hope the formatting comes across)

Projects
A B
C D
2014a Arts lab 0102287-08 001227


Result in Time
2014a Arts Lab
0102287-08 001227
 
It sound like you just want to do a basic VLOOKUP
formula. The Excel help module on this formula is fairly
detailed. Basically, you want to say =VLOOKUP(lookup
value, lookup range, column to return, TRUE/FALSE)

The lookup value is the cell you are trying to match, in
this case, A1 on Time.

The lookup range is the range where you expect to find the
matching cell. For this range, the column you expect to
match must be the leftmost and all the columns you want to
pull data from must be included. It will probably look
like Projects!$A$1:$D$200 Be sure to put in the dollar
signs so you anchor your range if you copy the formula
down.

To get the column to return, pretend the first column of
your lookup range is 1. Then count over to the column
holding the data you want to see.

TRUE/FALSE: Enter one of those words. If you pick False,
you'll only get exact matches. True will return the next
closest when there is no match, as long as your data range
is sorted in the correct order.

In general, be careful with VLOOKUPS that your range and
the lookup value are both the same type of format.
Sometimes, Excel has a cell that looks like a number, but
is really text. A vlookup won't work unless they're both
text or both numbers.

Your final formula should be something like =VLOOKUP
(A1,Projects!$A$1:$D$200,2,FALSE)

For the C&D concatenation, put the formula in twice with
the & sign between them (no spaces):

=VLOOKUP(A1,Projects!$A$1:$D$200,3,FALSE)&VLOOKUP
(A1,Projects!$A$1:$D$200,4,FALSE)

Hope this helps.

Jane
 
Thanks Jane for quick response. I will try it after the weekend. Probably
not till Tuesday or Wednesday though. Stay tuned I may need more help.

Robin
 
Ok, so far the formula looks like this:
=IF(ISBLANK(B30),"",IF(ISERROR(MATCH(B30,Projects!$A$2:$A$300,0)),"no
match",(VLOOKUP(B30,Projects!$A$2:$D$300,3,FALSE)&-VLOOKUP(B30,Projects!$A$1
:$D$300,4,FALSE))))

I have added some error trapping and it is working fine. Problem now is
getting the format of the final number correct on the "&" portion. What I
want is for example: 0345678-001. What I am currently getting is 345678-1. I
have tried formatting to custom as 0000000-000 but to no avail. In Projects
the columns are formatted to 0000000 and 000 respectively.

Rd
 
Back
Top