• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 133
  • Last Modified:

vlookup problem

Folks,
I've attached a workbook where I have been trying to look up a product based upon the most shipments for that product. Under Products I'd like to show which product is represented next to most shipments using vlookup and I'm not getting my product:
vlookupvlookup-issues.xlsm
0
Frank Freese
Asked:
Frank Freese
  • 5
  • 4
1 Solution
 
zalazarCommented:
You might try the following:
Insert into Cell Q7  
=INDEX($L$6:$N$15;MATCH(P7;$N$6:$N$15;0);1)

Open in new window

Copy cell Q7 down to Q8
So Q8 contains
=INDEX($L$6:$N$15;MATCH(P8;$N$6:$N$15;0);1)

Open in new window


Using INDEX and MATCH has the advantage that you do not have to sort the lookup range.
0
 
Frank FreeseAuthor Commented:
Problem:
Warning
Warning location
0
 
zalazarCommented:
It seems that I'm using a different separator (;) instead of a comma (,).
Can you please change the formulas to:
=INDEX($L$6:$N$15,MATCH(P7,$N$6:$N$15,0),1)
=INDEX($L$6:$N$15,MATCH(P8,$N$6:$N$15,0),1)

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Frank FreeseAuthor Commented:
Rats! I forgot to mention that this function is an array - sorry about that.
indexThe second work just fine - the first came back TRUE
0
 
zalazarCommented:
No problem. I'm not sure why it's coming back with TRUE.
I modified the Excel sheet and attached it. For me it's showing the correct value, does it still displays TRUE on your computer ?
vlookup-issues-1.xlsm
0
 
Frank FreeseAuthor Commented:
this is weird. yours works - mine doesn't. I even copied your formulas and got the same result. Here's my workbook. I don't see the difference - anywhere.
vlookup-issues.xlsm
0
 
Frank FreeseAuthor Commented:
In using a different worksheet everything worked - go figure and thank you
Great job!
0
 
zalazarCommented:
Very good to hear that it's working now.
I looked into the last posted "vlookup-issues.xlsm" and noticed that cell Q7 does contain the formula twice.
So in 1 cell:
=INDEX($L$6:$N$15;MATCH(P7;$N$6:$N$15;0);1)
=INDEX($L$6:$N$15;MATCH(P7;$N$6:$N$15;0);1)

Open in new window

When removing the second line, leaving only 1 line it's working ok.
Thank you very much for the points and grade !
0
 
Frank FreeseAuthor Commented:
What I found out was that I' set the format for the second cell at ";;;" and forgot to change it to "General". Just thought I'd let you know one of the problems was developer related.
Thanks again
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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