HLOOKUP Not Working

Question

Level 1

4 points

HLOOKUP Not Working

This is a Lacrosse Spreadsheet.

I'm trying to bring values from one sheet into another sheet which is sorted (I'm using the LARGE function). At the moment I have three values (Jersey, Position, PassRank) within my HLOOKUP Reference. I've used the LARGE function to pull over the Top 20 values in order to the new Sheet 'Best Players', I'm attempting to do a HLOOKUP (HLOOKUP(B2,PassRank Results::$B$1:$U$3,1,0)) where the value of B2 is contained within the range ($B$1:$U$3) but the formula says 'Value Not Found' (on my iPad), if I flip the exact match bit it comes back with the incorrect jersey number.

The interesting part is if I change the B2 value to Position/Jersey it works it just seems like it doesn't want to accept the PassRank value. Below is an image of the Reference table:

The below screen show is the Best Player Sheet:

The only other thing I can think of is Row 3 (upper picture) is populated based on Cell Reference (lower on the sheet), and Column B (lower picture) is referencing Row 3 via the Large function.

Any help would be greatly appreciated, I'm not Numbers master but I've seeming gotten more complex things to work within Numbers.

MacBook Pro with Retina display, OS X El Capitan (10.11.4)

Posted on May 12, 2016 6:30 AM

Reply

Answer 1

May 12, 2016 6:42 AM in response to wjbivens

Perhaps click the triangles and report what the error is they are reporting

Reply

Answer 2

wjbivens Author

Level 1

4 points

May 12, 2016 6:46 AM in response to Wayne Contello

Thanks Wayne, it shows the same/similar error as my iPad "HLOOKUP couldn’t find the requested value.".

From a testing purpose I did two other things to address my 'only other thing', I made direct reference search-for to the Cell on the PassRank sheet row-range (that didn't work) and then I a direct reference in the search-for where my row-range pulls its data.

Seems like it's having a hard/impossible time pulling numbers in my case.

Reply

Answer 3

May 12, 2016 7:01 AM in response to wjbivens

Please select the data table and paste it here in the forum, then post a screenshot that shows more of both tables

Reply

Answer 4

wjbivens Author

Level 1

4 points

May 12, 2016 7:10 AM in response to Wayne Contello

Below is the Data Table, named PassRank. Column A is Jersey on down the line.

Jersey #	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	Sum
Position	Attack	Attack	Attack	Attack	Attack	Attack	Attack	Attack	Attack	Attack	Attack	Attack	Attack	Attack	Attack	Attack	Attack	Attack	Attack	Attack
Final PassRank	0.276	0.367	0.326	0.829	2.007	0.240	1.802	2.458	0.175	0.273	1.831	1.553	0.391	2.821	0.220	0.204	1.225	0.000	0.000	0.000	17.000
Initial PassRank	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	20

Below is the Best Players sheet:

Below is a screen shot of the formula for A2 in the above table:

In an effort to see I could eliminate any 'Numbers' confusion I limited the search to columns N-P. I even attempted to turn off exact match and it still can't find the value in O2.

Reply

Answer 5

SGIII

Level 8

37,550 points

May 12, 2016 9:31 AM in response to wjbivens

I think the problem is that HLOOKUP always uses the top row of values to look up the value, whereas here you are using the third row (FinalPassRank). Lookups using INDEX MATCH are more flexible. You could do something like this:

The formula in A2, filled down, is:

=INDEX(PassRank Results::$1:$1,MATCH(B2,PassRank Results::$3:$3,0))

The MATCH finds on what column the value in B2 occurs in row 2 of the 'PassRank Results' table.

The INDEX then looks up the corresponding value for that column number in row 1 of 'PassRank Results'.

SG

Reply

Answer 6

wjbivens Author

Level 1

4 points

May 12, 2016 10:14 AM in response to SGIII

SGIII,

Thanks, that worked. After I figured that out I started on my next problem and figured out I could also do with LOOKUP using the following syntax: LOOKUP(C2,PassRank Results::$B$3:$U$3,PassRank Results::$B$1:$U$1)

LOOKUP seemed cleaner so after I got that working I left it.

Reply

Answer 7

SGIII

Level 8

37,550 points

May 12, 2016 11:44 AM in response to wjbivens

wjbivens wrote:

SGIII,

LOOKUP seemed cleaner so after I got that working I left it.

LOOKUP seems cleaner but it isn't. It's an ancient function from the very early days of spreadsheets and–unlike MATCH, VLOOKUP, and HLOOKUP–it doesn't have a parameter for an exact match. I recommend getting into to the habit of using INDEX MATCH. Some of the reasons why are mentioned here (as applicable to Numbers as to Excel).

SG

Reply