+ Reply to Thread
Results 1 to 2 of 2

Referencing a calculated value in VLOOKUP and INDEX-MATCH

  1. #1
    Registered User
    Join Date
    11-12-2007
    Posts
    5

    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. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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