Vlookup from a formula field

[Jim]

Hello,

I would like to ask for help. Am I able to vlookup from a field? Here is
what I'm doing:

- on sheet two I have a lookup field (Column 1 is the percentage, column 2
is the amount paid if that percentage is reached)

- in Sheet 1 I have a quota listed in A1; the actual amount reached in A2,
and the percent to quota in A3 (A2/A1)

In A4 I would like to have the lookup return the paid out amount based on
the percentage shown in A3, however I am receiving a #N/A.

The only thing I can think of is that I cannot use a vlookup for this type
of calculation.

Any suggestions on how I may be able to complete this task?

Thanks
Jim

 

[Pete_UK]

Your table needs to be sorted, and in your VLOOKUP formula you need to
omit the 4th parameter (or set it to TRUE).

It is unlikely that your calculated percentage would exactly equal the
percentages in your table.

If you are still stuck, post examples of your formula and your table.

Hope this helps.

Pete

 

[Eduardo]

Hi,
try

=VLOOKUP(C1,Sheet2!A:B,2,FALSE)

but I have some questions what if you have the same % more than once,
vlookup will bring the first amount it found,
is it possible to have same % with dif amounts ?

 

[Jim]

Eduardo,

Thanks for the help. The formula you provided I have tried, it is the one
returning the error. Any other suggestions about how to make this vlookup
work?

Your questions, the same percentage will not occur twice.

Thanks
Jim

 

[Eduardo]

Hi Jim,
Is the % in sheet2 calculated or manually entered, or come from a database.
Check if there is not any blank space at the beginning, you can use this
formula to remove any blank space and then copy as value on top

=trim(a1)

 

[Jim]

Eduardo,

Sheet Two is manually entered.

Sheet one is calculated.

I did confirm there are no blanks.

I can't help but think I'm soing something wrong. Any other thoughts?

Jim

 

[Eduardo]

Hi,
I test the formula and it's working for me, for example 2 % are you entering
it as 0.02