Solved

Excel Vlookup Issue: Need multiple columns of vlookup data

Posted on 2012-03-19
6
233 Views
Last Modified: 2012-06-27
Hi Experts,

See attached spreadsheet for example of what I am trying to do.
Lookup-Issue.xls

In Range A2:A10, I have a list of building names.  In columns B-K, rows 2-10, I have different levels that upgrades can be applied to these buildings.

In Range A13:A62, I have a list of all 50 levels.  In Range B13:B62, I created a simple CountIf statement that counts the total number buildings of that level in Range B2:K10.

What I need is to be able to display all Building Names in columns C:K next to the total number of buildings per level.  Is this possible with a VLookup?  I have created static data of what I am talking about in rows 13-17.

Any help would be greatly appreciated!

Regards,
Rob
0
Comment
Question by:RobStl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 37740626
Hi

In C13 and copied down & across,

=IF(COLUMNS($C13:C13)<=$B13,INDEX($A$2:$A$10,SMALL(IF($B$2:$K$10=$A13,ROW($A$2:$A$10)-ROW($A$2)+1),COLUMNS($C13:C13))),"")

Array formula. Confirmed with CTRL + SHIFT + ENTER

Kris
0
 

Author Comment

by:RobStl
ID: 37740651
Hi Kris,

Thank you for your comment.  When I put that formula into C13, it returns "#VALUE!" for some reason..  It says that there might be a circular reference..  Do you know why this might be for me?

Rob
0
 
LVL 4

Accepted Solution

by:
ltsweb earned 500 total points
ID: 37740682
In the attached spreadsheet, I used the formula in C3 through C62

=IF(COUNTIF($2:$2|$A13)>0|$A$2|"")

(replace the | with a , if you are hand typing into your spreadsheet).

This returns a value of the building name in the column.

Modify the formula slightly for the remaining rows (you can copy down):

=IF(COUNTIF($3:$3|$A13)>0|$A$3|"") for Building 2
=IF(COUNTIF($4:$4|$A13)>0|$A$4|"") for Building 3, etc.

You then get a table listing each building that exists for that Level (1-50).



Hope this works.
Lookup-Issue.xls
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:RobStl
ID: 37740718
Hi ltsweb,

That will actually do for what I need :)  I finished the remaining columns and it worked and is simple to understand.

Thanks!
Rob
0
 
LVL 4

Expert Comment

by:ltsweb
ID: 37740728
Great Rob!
I have to say, I liked the other solution from Kris, but I couldn't get it to work either!  I am glad the simple solution worked for you!

Regards!
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 37742083
Hi

PFA.

Kris
Lookup-Issue.xls
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question