Solved

# I need an excel formula

Posted on 2011-03-01
Medium Priority
228 Views
Ok, gonna try to explain this as best as I can.

I need a formula on the Summary tab (see the yellow) that will essentially insert the respective Code with the Project Number. I have been able to manage to to that, but when I get to the "Totals" and "Group B projects" on the Pojects tab, it ithrows off my formula by two.

Is there a formula to ignore the two blank values on the Summary Tab in cells B 24 and 25? Because wehn i copy them down the column, it throws off my Index Match formula. See the red on the Summary tab.

=INDEX(Projects!\$B\$4:\$B\$45,MATCH(Projects!C4,Projects!\$C\$4:\$C\$45,0),0).
0
Question by:wrt1mea
• 5
• 3
• 2
• +1

LVL 1

Author Comment

ID: 35008717
Doh! Here is the attachment.
EX-Exch-Test.xlsx
0

LVL 45

Expert Comment

ID: 35008875
wrt1mea,

You could use:

=VLOOKUP(A4,projects,2,0)

It's in the attached file.

Patrick
wrt1mea-01.xls
0

LVL 6

Expert Comment

ID: 35008898
Or

=INDEX(Projects!\$A\$4:\$C\$275,MATCH(A4,Projects!\$A\$4:\$A\$275,0),2)

If you prefer.

A>
0

LVL 45

Accepted Solution

patrickab earned 2000 total points
ID: 35009062
Or you could use:

=INDEX(Projects!\$A\$4:\$B\$44,MATCH(Summary!A4,Projects!\$A\$4:\$A\$44,0),2,1)
0

LVL 1

Author Comment

ID: 35009102
patrickab,

I was attempting to use an index match...what does the 2,1 at the end help do?
0

LVL 45

Expert Comment

ID: 35009138
>.what does the 2,1 at the end help do?

Column 2, area 1 - only one area was specified as only one range was declared.

BTW ther is no advantage in using Index as opposed to VLOOKUP() in this instance.
0

LVL 1

Author Comment

ID: 35009202
Well, I should have done a better job with the example and explanation. the main information I will be referencing will not be in the first column...
0

LVL 50

Expert Comment

ID: 35009294
Patrick's VLOOKUP suggestion would seem to be fine, you can always adjust the formula so that the lookup range is a different column.

INDEX/MATCH is also possible, sometimes preferable, when the data to be returned is in a column to the left of the lookup range then INDEX/MATCh is the standard approach but that doesn't seem to be the case here, can you explain what you need to do that you can't do with VLOOKUP?

regards, barry
0

LVL 1

Author Comment

ID: 35009313
OK,I always thought that the limitation to vlookup was the the particular cell you are referecing had to be in the first column. If I was wrong about that, my apologies for not completely understanding. The index match formula he provided worked great.
0

LVL 50

Expert Comment

ID: 35009368
With VLOOKUP the lookup value is always looked up in the first column of the range you specify, so with

=VLOOKUP(A4,B:C,2,0)

A4 is looked up in the first column of B:C, that's column B, of course if you want to lookup a value in C and return the corresponding row from D then you can just change the range, i.e.

=VLOOKUP(A4,C:D,2,0)

regards, barry
0

LVL 1

Author Closing Comment

ID: 35009431
Works great and thanks for all the help
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.