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
Solved

Excel Vlookup Issue: Need multiple columns of vlookup data

Posted on 2012-03-19
6
230 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
  • 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

856 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