Index & Match based on two columns

[Beh162]

My current formula =index(D:D,MATCH("050-Administration",B:B,0)) Which returns to me 77. I realize in two different boxes I need find the 77 and also 3059. I'm not sure how to ask the Index & Match to
Look for 050-admin & Hours and return the 77 and then another to look for 050-admin & Dollars and return the 3059.

My attempt trying to replicate an array formula I found was =(index(D:D,MATCH(1,"050-Administration",B:B,0)*("Hours",c:c,0))) but that included an error and I'm having a hard time understanding looking in two places.
Thanks so much!

 

[awoohaw]

There are two things you may want to think about.
1. your index/match functions need to be separated.

=index(D:D,MATCH(1,"050-Administration",B:B,0)  *    INDEX(D:D,Match("Hours",c:c,0))

2. You can make the functions more useful by having your lookup data and your output/calcluations in separate places (or maybe you did that to display here).

But something you should also consider is to never ever have merged cells. If you want to hide the information in merged cells change the font of the cells to match the cell background color.

 

[AlanY]

If your data is always has dollars after the hours in the same column, you can get the second match with

=index(D:D,MATCH("050-Administration",B:B,0)+1)

 

[Beh162]

There are two things you may want to think about.
1. your index/match functions need to be separated.

=index(D:D,MATCH(1,"050-Administration",B:B,0)  *    INDEX(D:D,Match("Hours",c:c,0))

2. You can make the functions more useful by having your lookup data and your output/calcluations in separate places (or maybe you did that to display here).

But something you should also consider is to never ever have merged cells. If you want to hide the information in merged cells change the font of the cells to match the cell background color.

Unfortunately, this is a report that in auto generated and I'm pulling data from it. I had a feeling the merged was an issue.

 

[Beh162]

If your data is always has dollars after the hours in the same column, you can get the second match with

=index(D:D,MATCH("050-Administration",B:B,0)+1)

Yes, the data will always appear just like that Dollars below. I plugged in that formula and I did receive the 3059. Now hypothetically speaking if I wanted the third row OT Hours would I do +2?

 

[awoohaw]

is it possible you can post a mini workbook using the xl2bb add in so we can work with your data as it is? (anonymized for privacy).

 

[AlanY]

Yes, the data will always appear just like that Dollars below. I plugged in that formula and I did receive the 3059. Now hypothetically speaking if I wanted the third row OT Hours would I do +2?

yes, it should.