Vlookup with more than 1 criteria

Good morning,

In the attached exampe sheet i have a vlookup working through a roster to extract just the shift headings (LC1, LC2 etc) and the aligning start, finish and shift lengths from the adjacent columns.

I now need to add in different role names. You will see in the end of column I there are now shifts called "Operational Advanced" with a number following.

What I need to do is amend my formula so that either of these "Roles" will come up in the table under column B.

There is potential for more different role names in the future, so ideally I would like to be able to expand this method as required.

Thanks in advance

Darren.

Hello,

One way is that you use IFERROR and put in a second VLOOKUP

Please Login or Register  to view this content.

The advantage of this is that it is very light, making the calculating very fast.

The downside is, however, it will be very hard to add another condition on it. In that case, you might want to look for an Array formula like this

Please Login or Register  to view this content.

Remember to hod Ctrl-Shift and hit Enter when you confirm. If you see the formula being wrapped inside a { } then you did it right.

Here is your sample file illustrating both formula.

Thanks Lemice,

So I can check my understanding with the array method, if I need to add another different "Role" name, i would simply add it as follows:

Please Login or Register  to view this content.

Did I get that correct?

Yes you are right, just add another ISNUMBER into it and it will work just fine.

Awesome thanks so much .... just what i needed.

Glad that I could help.

Have a great day.

an alternative method...

add a helper column (I used P) and copy this down...
=IF(OR(I3="",ISNUMBER(I3)),"",I3)

Then in B3, copied down...
=IFERROR(INDEX($P$3:$P$200,MATCH(0,INDEX(COUNTIF($B$2:B2,$P$3:$P$200),0,0),0)),"")
and change the formula in C3 to this, coped down and across...

=VLOOKUP($A3,$I$3:$O$250,COLUMN()-1,FALSE)