Vlookup with IF's and multiple conditions

[PhantomJoe]

I'm not sure why this is stumping me but I've been struggling to find the right solution to solve for the following.

I have a table of data that is used to determine cancellation percentages based on a number of factors.

The first factor is the "Position Assignment" (found in column U) and values in this column can be one of 8 values like "Direct Hire Business" and "Direct Hire Technical".

The next check is against the number of "Days Open" (column R) for the requisition. Depending on the specific Position Assignment the ranges will vary. Here's an example:

Position Assignment	Days Open	% of Fee	Days Open	% of Fee	Days Open	% of Fee
Direct Hire Technical	<4 Days	0%	5-14 Days	50%	15+ Days	100%
FCG/LP	<30 Days	0%	31-44 Days	25%	45+ Days	100%

<tbody>
</tbody>

<table border="0" cellpadding="0" cellspacing="0" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;"="" width=""><colgroup><col></colgroup><tbody></tbody></table>



So the above table contains the logic Cancellation Fees based on Position Assignments and if it's closed within the X days then the % of Fee will be assigned based on how long the Req was open. So if it was a Direct Hire Technical and open for 9 days then the % of Fee would be 50%.

The formula I think I'm looking for would first do a lookup based on the Position Assignment and then check the Days Open column to see what range it falls in then assign the % of Fee based on those 2 factors.

Is this doable with an if/vlookup formula?

 

[Aladin Akyurek]

@ PhantomJoe

Try to re-arrange the look up table you have as follows:

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Position Assignment	Days Open			% of Fee
2​	Direct Hire Technical	4​	5​	15​	0%​	50%​	100%​
3​	FCG/LP	30​	31​	45​	0%​	25%​	100%​

This table is easier to use as shown below:

Row\Col	A​	B​	C​
5​	Direct Hire Technical	2​	0​
6​	Direct Hire Technical	4​	0​
7​	Direct Hire Technical	7​	0.5​
8​	FCG/LP	32​	0.25​
9​	FCG/LP	50​	1​
10​	FCG/LP	20​	0​

In C5 enter (and copy down for other pairs of look up values)...

=IFNA(LOOKUP($B5,INDEX($B$2:$D$3,MATCH($A5,$A$2:$A$3,0),0),INDEX($E$2:$G$3,MATCH($A5,$A$2:$A$3,0),0)),0)

If you get a #NAME ? error, replace IFNA with IFERROR.

 

[PhantomJoe]

@ PhantomJoe

Try to re-arrange the look up table you have as follows:

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Position Assignment	Days Open			% of Fee
2​	Direct Hire Technical	4​	5​	15​	0%​	50%​	100%​
3​	FCG/LP	30​	31​	45​	0%​	25%​	100%​

<tbody>
</tbody>

This table is easier to use as shown below:

Row\Col	A​	B​	C​
5​	Direct Hire Technical	2​	0​
6​	Direct Hire Technical	4​	0​
7​	Direct Hire Technical	7​	0.5​
8​	FCG/LP	32​	0.25​
9​	FCG/LP	50​	1​
10​	FCG/LP	20​	0​

<tbody>
</tbody>

In C5 enter (and copy down for other pairs of look up values)...

=IFNA(LOOKUP($B5,INDEX($B$2:$D$3,MATCH($A5,$A$2:$A$3,0),0),INDEX($E$2:$G$3,MATCH($A5,$A$2:$A$3,0),0)),0)

If you get a #NAME ? error, replace IFNA with IFERROR.

This worked perfectly. All I had to do was replace the IFNA with IFERROR and it worked like a charm. Thank you so much!

 

[Aladin Akyurek]

This worked perfectly. All I had to do was replace the IFNA with IFERROR and it worked like a charm. Thank you so much!

You are welcome. Thanks for the update.

 

[markmzz]

I'm not sure why this is stumping me but I've been struggling to find the right solution to solve for the following.

I have a table of data that is used to determine cancellation percentages based on a number of factors.

The first factor is the "Position Assignment" (found in column U) and values in this column can be one of 8 values like "Direct Hire Business" and "Direct Hire Technical".

The next check is against the number of "Days Open" (column R) for the requisition. Depending on the specific Position Assignment the ranges will vary. Here's an example:

Position Assignment	Days Open	% of Fee	Days Open	% of Fee	Days Open	% of Fee
Direct Hire Technical	<4 Days	0%	5-14 Days	50%	15+ Days	100%
FCG/LP	<30 Days	0%	31-44 Days	25%	45+ Days	100%

<tbody>
</tbody>

Hi!

Another table format and with OFFSET function.

If is possible the table format of the range A1:D7, then the formula below can help you:

=IFERROR(LOOKUP($R2,N(OFFSET($B$1,{1;3;5},MATCH($U2,$B$1:$D$1,0)-1)),
N(OFFSET($B$1,{2;4;6},MATCH($U2,$B$1:$D$1,0)-1))),0)

	A	B	C	D	E	Q	R	S	T	U	V	W	X
1		Direct Hire Technical	FCG/LP	Direct Hire Business			Days Open			Position Assignment		% of Fee
2	Days Open	0	0	0			32			Direct Hire Business		0,1
3	% of Fee	0,01	0,02	0,03			2			Direct Hire Technical		0,01
4	Days Open	5	31	10			18			Direct Hire Technical		1
5	% of Fee	0,5	0,25	0,1			13			Direct Hire Business		0,1
6	Days Open	15	45	50			11			Direct Hire Technical		0,5
7	% of Fee	1	1	1			30			FCG/LP		0,02
8							43			FCG/LP		0,25
9							27			Direct Hire Business		0,1
10							47			FCG/LP		1
11							8			Direct Hire Technical		0,5
12							23			FCG/LP		0,02
13							46			Direct Hire Technical		1
14							43			FCG/LP		0,25
15							39			Direct Hire Technical		1
16							48			Direct Hire Business		0,1
17							21			FCG/LP		0,02
18							20			Direct Hire Business		0,1
19							41			Direct Hire Business		0,1
20							5			FCG/LP		0,02
21
***	***********	********************	********	*******************	**	**	***********	**	**	********************	**	*********	**

<tbody>
</tbody>

Markmzz