Vlookup - return a result based upon referencing the same column-index- number in the same cell

[jgold20]

I would like to use Vlookup to return a result based upon referencing the same column-index- number in the same cell. I have the below calculation from spreadsheet A:

=IF(A9="","",IF(G6=0,0,(A6*D9)-(A6-G6)*D9))

In the above, A6 is a deposit %

I wouldlike to convert to a Vlookup:

=IF(A9="","",IF($G$3=0,0,VLOOKUP(C3,'Company Info'!$A$1:$M$18,3,FALSE)*D9-(Deposit %-g6)*D9))

In the above, the column-index-number 3 is the deposit %

I WANT TO AVOID A HELPER COLUMN IF POSSIBLE

 

[Toadstool]

Hi JGold20,

So if the third column of the VLOOKUP search is Deposit % then I just substituted.

Company Info looks like

JGold20.xlsx
	A	B	C
1	Company	CEO	Deposit %
2	ACME	Sue	12.00%
3	Widgit	Fred	13.50%
4	Noonday LLC	Bert	15.00%
5	Xample	Vanessa	16.50%
6	Koniten	George	18.00%
7	Lambda3	Sarah	19.50%
Company Info

Sheet A looks like

JGold20.xlsx
	A	B	C	D	E	F	G	H	I
1									Result
2									0.28
3			Xample				1
4
5
6							4%
7
8
9	3			7
A

Cell Formulas
Range	Formula
I2	I2	=IF(A9="","",IF($G$3=0,0,VLOOKUP(C3,'Company Info'!$A$1:$M$18,3,FALSE)*D9-(VLOOKUP(C3,'Company Info'!$A$1:$M$18,3,FALSE)-G6)*D9))

 

[jgold20]

THE 1ST VLOOKUP SUBTRACTING THE 2ND VLOOKUP RESULTS IN A 0

 

[Toadstool]

THE 1ST VLOOKUP SUBTRACTING THE 2ND VLOOKUP RESULTS IN A 0

Please don't shout.

If the Deposit% isn't the 3rd column of the Company Info for the company designated in C3 then please explain its origin.

The two VLOOKUPS don't necessarily net to zero as it depends on the other variables. In this case:

• The first VLOOKUP retrieves 0.165 (16.5%) which is multiplied by 7 (D9) which gives 1.155.
• The second VLOOKUP retrieves the same 0.165 and subtracts 0.04 (G6) to gives 0.125 which is multiplied by 7 (D9) to give 0.875.
• This is then subtracted from the previously calculated 1.155 to give 0.28, as shown in my example.

If you're trying to avoid executing two identical VLOOKUPs then you'll need the beta version of Excel with the new LET() function to calculate and store intermediary values.

It would be best if you could use XL2BB to post a worked example of your challenge.

 

[jgold20]

Please don't shout.

If the Deposit% isn't the 3rd column of the Company Info for the company designated in C3 then please explain its origin.

The two VLOOKUPS don't necessarily net to zero as it depends on the other variables. In this case:

• The first VLOOKUP retrieves 0.165 (16.5%) which is multiplied by 7 (D9) which gives 1.155.
• The second VLOOKUP retrieves the same 0.165 and subtracts 0.04 (G6) to gives 0.125 which is multiplied by 7 (D9) to give 0.875.
• This is then subtracted from the previously calculated 1.155 to give 0.28, as shown in my example.

If you're trying to avoid executing two identical VLOOKUPs then you'll need the beta version of Excel with the new LET() function to calculate and store intermediary values.

It would be best if you could use XL2BB to post a worked example of your challenge.

I was not shouting, I did not realize caps lock was in.

The deposit % is in column 3.

1. The first Vlookup retrieves 30% multiplied by $1100.00 (D9) which is $330.00
2. I need to now lower the % from 30% to 25% multiplied by $1100.00 (D9) which is $55.00

• When I use a formula - =(30%*G9)-(30%-5%)*G9 it works.
• When I use the suggested Vlookup - (iF(A9="","",IF($G$3=0,0,VLOOKUP(C3,'Company Info'!$A$1:$M$18,3,FALSE)*D9-(VLOOKUP(C3,'Company Info'!$A$1:$M$18,3,FALSE)-G6)*D9))
  • The result is - 0.00

 

[Toadstool]

So your expected result is $55?

Then if I change the % for Xample to 30%

JGold20.xlsx
	A	B	C
1	Company	CEO	Deposit %
2	ACME	Sue	12.00%
3	Widgit	Fred	13.50%
4	Noonday LLC	Bert	15.00%
5	Xample	Vanessa	30.00%
6	Koniten	George	18.00%
7	Lambda3	Sarah	19.50%
Company Info

I do get the expected result

JGold20.xlsx
	A	B	C	D	E	F	G	H	I	J
1										Result
2										$ 55.00
3			Xample				1
4
5
6							8
7
8
9	3			$1,100
A

Cell Formulas
Range	Formula
J2	J2	=IF(A9="","",IF($G$3=0,0,VLOOKUP(C3,'Company Info'!$A$1:$M$18,3,FALSE)*D9-(VLOOKUP(C3,'Company Info'!$A$1:$M$18,3,FALSE)-5%)*D9))

 

[jgold20]

=IF(A9="","",IF($G$3=0,0,VLOOKUP(C3,'Company Info'!$A$1:$M$18,3,FALSE)*D9-(VLOOKUP(C3,'Company Info'!$A$1:$M$18,3,FALSE)-5%)*D9))

Thank you. It works,