vlookup for 2 criterias

D

Dode

Good morning,

I'm trying to use VLookup to populate the second table below using the data
from table 1, but there are two lookup matches that need to occur (Segment &
ReportType) and I am unable to make it work.

TABLE 1 : a1:c9
Segment/ReportType/Hours
AAA/Break/4620
AAA/EmpEng/690
BBB/Break/2435
BBB/Coach/16285
CCC/Coach/50744
CCC/ Empeng/2940

TABLE 2 : e1:I5
Segment/BREAK/COACH/EMPENG
ASC
BBB
BCB
CCC

This is the formula I'm using for one criteria and have tried to alter for
two criterias but without any succes..
=IF(ISERROR(VLOOKUP(K$360,wksheetHRS,2,FALSE)),"0",VLOOKUP(K$360,wksheetHRS,2,FALSE))

Thanks
 
P

Pete_UK

In table 1 insert a new column C and put this formula in C2:

=A2&B2

with a suitable heading in C1. Copy this down to C9.

Then your formula in G2 becomes:

=IF(ISNA(VLOOKUP($F2&G$1,$C$2:$D$9,2,0)),"",VLOOKUP($F2&G$1,$C$2:$D
$9,2,0))

Copy this across to I2, and then copy G2:I2 down to row 5.

Hope this helps.

Pete
 
D

Dode

Thanks for taking the time to respond. Due to the set up of the report I
could not concantenate the columns, however, I was able to resolve with this
formula :
=INDEX(Worksheet!$M$250:$M$351,MATCH(1,($B361=Worksheet!$K$250:$K$351)*(D$359=Worksheet!$L$250:$L$351),0))

thanks!
 
P

Pete_UK

Okay, well I'm glad that you got something that worked for you. Thanks for
feeding back.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top