vlookup for 2 criterias

[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

 

[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

 

[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!

 

[Pete_UK]

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

Pete