# Referencing a calculated value in VLOOKUP and INDEX-MATCH

1. ## Referencing a calculated value in VLOOKUP and INDEX-MATCH

The problem I'm having is that I can't get either VLOOKUP or INDEX-MATCH queries to work if the cell I'm having it function use to reference the data is a calculation.

ie:

=INDEX(Model!\$B\$2:\$B\$269,MATCH(J11,Model!\$A\$2:\$A\$269,FALSE),1)

where

J11 = (F18+F19)/F19 [note: F18 and F19 are entered values]

The moment I change J11 to a value (say 1.136), the lookups work. Is there anyway to get around this?

Thanks!

2. Hello JillianRuth

As it stands there needs to be an exact match so if you put 1.136 in J11 you get a result, presumably because 1.136 appears somewhere in Model!\$A\$2:\$A\$269

When you do a calculation like (F18+F19)/F19, the result may display as 1.136 but the "underlying value" probably has more decimal places so you don't get an exact match.

What values do you have in Model!\$A\$2:\$A\$269? If this column is sorted ascending you might want to change MATCH to return a "closest match" (just remove FALSE) or perhaps you can round the calculation in J11 so that you get an eaxct match, e.g.

=ROUND((F18+F19)/F19,3)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1