Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel Vlookup Issue: Need multiple columns of vlookup data

Posted on 2012-03-19
6
Medium Priority
?
236 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 2000 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
Technology Partners: 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!

 

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

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.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

650 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