Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

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
0
Bright01
Asked:
Bright01
  • 5
  • 2
  • 2
3 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Saqib Husain, SyedEngineerCommented:
Try match combined with index
0
 
byundtCommented:
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
 
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
 
byundtCommented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now