Solved

Excel Vlookup Issue: Need multiple columns of vlookup data

Posted on 2012-03-19
6
227 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
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…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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 …

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now