• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

I need an excel formula

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
wrt1mea
Asked:
wrt1mea
  • 5
  • 3
  • 2
  • +1
1 Solution
 
wrt1meaAuthor Commented:
Doh! Here is the attachment.
EX-Exch-Test.xlsx
0
 
patrickabCommented:
wrt1mea,

You could use:

=VLOOKUP(A4,projects,2,0)

It's in the attached file.

Patrick
wrt1mea-01.xls
0
 
akajohnCommented:
Or

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


If you prefer.


A>
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
patrickabCommented:
Or you could use:

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

I was attempting to use an index match...what does the 2,1 at the end help do?
0
 
patrickabCommented:
>.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
 
wrt1meaAuthor Commented:
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
 
barry houdiniCommented:
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
 
wrt1meaAuthor Commented:
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
 
barry houdiniCommented:
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
 
wrt1meaAuthor Commented:
Works great and thanks for all the help
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now