TickHogan
Level 1 (0 points)

Q: VLOOKUP not working in Numbers 3.6.1

Hi,

 

First time in this forum, but I have had a thorough read through similar problems, but they have not helped me. I am sure I am just missing something simple here, but I am getting the red triangle when I am using the VLOOKUP function with 'Exact match' criteria in Numbers 3.6.1 OSX Yosimite 10.10.5. Even when using 'Close match', I am getting unexpected results. I have ensured that all columns are formatted as text, and even tried using formatted numbers in previous attempts. I just cannot fault find this! Can somebody please tell me what i am doing wrong?

 

Screen Shot 2016-01-02 at 7.37.36 am.png

MacBook Pro, OS X Yosemite (10.10.5)

Posted on Jan 1, 2016 1:54 PM

by t quinn,Solvedanswer
t quinn t quinn
Level 5 (5,012 points)
Mac OS X
A:

Hi TickHogan,

 

VLOOKUP() will search for your value in the leftmost column and return the value in the return column. So if columns A and B were reversed in your example all would be well.

 

I have taken to using INDEX MATCH for my lookup needs. It gives me better control and I believe it is (slightly) less resource intensive.

Screen Shot 2016-01-01 at 10.03.15 PM.png

Depending on where you use MATCH() within the INDEX() formula you can use this for an HLOOKUP() also.

 

quinn

Posted on Jan 1, 2016 10:12 PM

Close
TickHogan

Q: VLOOKUP not working in Numbers 3.6.1

  • All replies
  • Helpful answers

  • by t quinn,Solvedanswer

    t quinn t quinn Jan 1, 2016 10:12 PM in response to TickHogan
    Level 5 (5,012 points)
    Mac OS X
    Jan 1, 2016 10:12 PM in response to TickHogan

    Hi TickHogan,

     

    VLOOKUP() will search for your value in the leftmost column and return the value in the return column. So if columns A and B were reversed in your example all would be well.

     

    I have taken to using INDEX MATCH for my lookup needs. It gives me better control and I believe it is (slightly) less resource intensive.

    Screen Shot 2016-01-01 at 10.03.15 PM.png

    Depending on where you use MATCH() within the INDEX() formula you can use this for an HLOOKUP() also.

     

    quinn

  • by TickHogan,

    TickHogan TickHogan Jan 1, 2016 10:11 PM in response to t quinn
    Level 1 (0 points)
    Jan 1, 2016 10:11 PM in response to t quinn

    Thanks very much t quinn,

     

    I knew it was something i was doing wrong, just couldn't pick it. I will look at your alternative method, but I was actually attempting to make the spreadsheet lookup a value from a range of cells with multiple columns and rows, and if found, return the left most column value for the row that the found value was in. I just simplified my problem to first principles during my fault finding, and to post here. Now that you have explained VLookup to me properly though, i might be able to nut it out for myself.

     

    Thanks

  • by t quinn,

    t quinn t quinn Jan 2, 2016 11:41 AM in response to TickHogan
    Level 5 (5,012 points)
    Mac OS X
    Jan 2, 2016 11:41 AM in response to TickHogan

    Hi TickHogan,

     

    I am not sure that Numbers has the array capabilities to do wht you want. If you get stuck please feel free to repost a more specific question.

     

    quinn