VLOOKUP not working in Numbers 3.6.1

Question

Level 1

0 points

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?

MacBook Pro, OS X Yosemite (10.10.5)

Posted on Jan 1, 2016 1:54 PM

Reply

Answer 1

t quinn

Level 5

7,063 points

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.

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

quinn

Reply

Answer 2

TickHogan Author

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

Reply

Answer 3

t quinn

Level 5

7,063 points

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

Reply