0

I am attempting to look up (VLOOKUP) information using the results of an INDEX/MATCH formula which always results in a #NA result.

=VLOOKUP(INDEX(DATA1!$H$3:$H$270,MATCH(B4,DATA1!$M$3:$M$270,0)),DATA2!$A$2:$A$500,6,TRUE)

Alternatively, I have tried to break the formula into two stages, adding the formula below into E2:

=INDEX(DATA1!$H$3:$H$270,MATCH(B4,DATA1!$M$3:$M$270,0)

and then in a separate field use the VLOOKUP formula referencing E2:

=VLOOKUP(E2,DATA2!$A$2:$A$500,6,TRUE)

Is there any way I can complete this formula using the output from another formula?

update on my original question I am getting the value in E2 - I am trying to us the data calculated from that field to complete Vlookup but it doesn't work. When I try and use that data for the next formula which is =VLOOKUP(E2,DATA2!$A$2:$A$500,6,TRUE)I received #NA

4
  • So, what happens? Are you getting the value in E2 that you expect? If you rewrite the third formula with the value of E2 plugged in as a constant, what happens? Please do not respond in comments; edit your question to make it clearer and more complete. Mar 9, 2017 at 1:48
  • I am getting the value in E2 - I am trying to us the data calculated from that field to complete Vlookup but it doesn't work. When I try and use that data for the next formula which is =VLOOKUP(E2,DATA2!$A$2:$A$500,6,TRUE)I received #NA Mar 9, 2017 at 4:27
  • @Nicolle Barratt your Vlookup is wrong the Lookup array does not contain column 6, it should be DATA2!$A$2:$F$500 like Sir Adelaide wrote kindly change it
    – yass
    Mar 9, 2017 at 14:27
  • Let’s step back and think about what you’re saying. If I understand you correctly, you seem to be saying that VLOOKUP( 42 , DATA2!$A$2:$A$500, 6, TRUE) fails (assuming that the value of E2 is 42) — so that should be the focus of your question.  If a formula fails when it stands by itself, it doesn’t make sense to worry about how to use a formula output as part of another formula. You might want to do some research on debugging, problem isolation, and simplification. Mar 9, 2017 at 16:35

1 Answer 1

0

Your VLOOKUP says to use the 6th column for the result, but you've only given it one column of data, thus the error.

You can either give VLOOKUP $A$2:$F$500 (and I'd recommend FALSE is usually better than TRUE), or else switch to using OFFSET:

=OFFSET($F$1,MATCH(B4,DATA1!$M$3:$M$270,0),0)
2
  • Apologies I transposed the information incorrectly, the formula was actually =VLOOKUP(INDEX(DATA1!$H$3:$H$270,MATCH(B4,DATA1!$M$3:$M$270,0)),DATA2!$A$2:$J$500,6,TRUE). Mar 9, 2017 at 4:34
  • I also added TRUE because the information captured as part of index match sequences that is referenced for the VLOOKUP in Data1 is only part of the original number -so not exact. My agenda is match 2 reference # in a worksheet and capture a new number, then I want to use that new number as reference to get more data from a different worksheet. Hope this makes sense Mar 9, 2017 at 4:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .