CODEWITHSUNDEEP
arraysexceldistinct-values
Gerry P.
Gerry P.

Reputation: 11

Excel Vlookup to a table array which has formulas returning values

Here is my formula that is not successful in returning a valid response from the table array.

=IFERROR(IF(X72=27,VLOOKUP(AE72,'Rates for Medical Plans 2018'!$B$64:$C$99,2,FALSE),IF(X72=52,VLOOKUP(AE72,'Rates for Medical Plans 2018'!$B$64:$C$99,2,FALSE),)),)

Main Data

Main data

Table Array

Table Array

The table array is a series of calculations where I pull in a specific rate and calculate a one penny variance either negative or positive from the main rate. Where I am encountering issues is that apparently the calculated values in the table array and not matching with the values in the main data located in column "AE" as shown in graphic 1. If I hard key the values, in the table array, the formulas work (in the main data column "AD") and I am able to populate column "AD" with the correct values from the lookup.

Upvotes: 0

Views: 713

Answers (1)

Alex P
Alex P

Reputation: 12489

Try using this on your Plan Cost values in column B of Table Array:

=Round(number, 2)  //Where `number` is B3, B4, B5 etc...

I suspect your Plan Cost values are stored as (e.g.) 379.70112 which displays as 379.70. If you VLOOKUP on that value you will get a fail i.e. 379.70112 does not equal 379.70.

Upvotes: 1

Related Questions