Fixing =Lookup

EE Pros,

I have a problem with Lookup.  I've attached the sheet I'm trying to do the function with.  For some reason it will not work in cells L41, M41, N41 and I cannot figure out why.



Please see if there is "a tweek" to fix this.

Thank you in advance,

B.
Lookup-fix.xlsm
Bright01Asked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
Following Saqibh's advice, your formula in cell L32 would be:
=INDEX(D$3:D$27,MATCH($I32,$G$3:$G$27,0))         may be copied down and across

And in L41:
=INDEX(D$3:D$27,MATCH($I41,$H$3:$H$27,0))
0
 
Saqib Husain, SyedEngineerCommented:
Lookup requires the looked up list to be in ascending order else the results would be unpredictable.
0
 
Bright01Author Commented:
I think there is a way around that using v or h lookup and a False reading..............

any idea?

B.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Bright01Author Commented:
The data changes and is random so I cannot simply sort the field once and rely that it is always assending.  Which field has to be assending?
B.
0
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Try match combined with index
0
 
Bright01Author Commented:
how?  is match a function?  how do you combine it with index?  can you use the sheet I sent to show me?

b
0
 
Bright01Author Commented:
got it.  ty byundt...let me try it
0
 
byundtConnect With a Mentor Commented:
As a general rule, you are better off using INDEX & MATCH, VLOOKUP or HLOOKUP when an exact match is required.

LOOKUP is on the Microsoft payroll for historical reasons primarily. It still has specialized applications when you want the last occurrence of a text or numeric value, perhaps with one or more criteria. But you will frequently get into trouble if trying to use it for ordinary VLOOKUP type problems because LOOKUP in such situations requires the data to be sorted in ascending order and will return an answer even if there is no exact match. VLOOKUP and MATCH are better behaved in such circumstances.
0
 
Bright01Author Commented:
Brilliant!  Thank you.  And learned something new on Indexing and Matching.

Thank you very much!

B.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.