?
Solved

Excel Vlookup Issue: Need multiple columns of vlookup data

Posted on 2012-03-19
6
Medium Priority
?
235 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
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.

 

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

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.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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