How do I get a vlookup to spit out multiple values?

like
Posting as :
works at
You are currently posting as works at

You can create a new ID to use for your vlookup by concatenating value x and countif built for value x’s column
After that you just pull it by the concatenated ID (e.g., value x1, value x2, etc.)

like

The idea behind countif is to generate a count of the values you are looking for in each lookup value. I.e., how many distinct values of indicator 2 are attributable to each value of Indicator 1. You then use the number generated by countif to build a new ID

Post Photo
like

I’d do a text join on an array formula probably. But to me, it seems doubtful that vlookup is the best solution for the problem you’re trying to solve. Can you clarify what you’re trying to do?

like

Use index match... always better than vlookup

likefunny

And Deloitte again missed the point of the question and just tried to show off by saying something she/he read in another context.

like

Elaborate?

like

{IFERROR(INDEX($C$3:$C$19000,SMALL(IF($B$3:$B$19000=$E3,ROW($C$3:$C$19000)-MIN(ROW($C$3:$C$19000))+1),COLUMNS($E$3:E3))),"")}

Post Photo
like

Not the cleanest formula, but here’s how to do it. Hypothetical example in picture shows store number then product type. Summarizing table shows all products by store number

Are the values in 2 separate columns? Then you need to do 2 separate vlookups. Otherwise I don’t understand what you mean

like

You can do a first index based on match then do a second index but use indirect to force a start point at first match row

like

Oooh i think i like this lots

I have value x and it has two designated line items on a tab and I need to pull both in into my new tab, but Vlookup only gives me the first value

You could do two separate vlookups then concatenate in a separate column

Sum if?

Read into array formulas, but make sure you protect the cells if someone else is going to work with that file as people tend to destroy arrays.
Find a good way here: https://www.get-digital-help.com/how-to-return-multiple-values-using-vlookup-in-excel/

You can't.

You could do something dirty (or hot if you have a formula fetish) with offsets and matches but your formulae will look horrible and you need to hardcode a number of possible matches.

You could put your look up dataset into a pivot table with your keys the rows and your return values as the column, then create a new column concatenating all the return values. Then look up to that.

Would it be easier in your situation to use a Pivot Table to organize the data?

Related Posts

Best advice to someone looking to switch LOS from Tax to Advisory? I think my contributions would be more appreciated there. Thoughts?

like

I did this manually but would there be a way to pivot the dates (day/month) in the rows years in columns with raw data being (date/month/year) needed a visual of same day different year

Post Photo

Are personal websites good as a substitute for a resume, or should I stick to the traditional one-page paper?

like

EY Marriott code?

like

Anyone have advice for talking to teachers in your school that are upset with you?
My 4th grade class didn’t show up today because the teachers are upset that student behaviors that are an issue in music are carrying over to their classroom. I understand their frustration, but nothing was communicated with me. I even thought things were going better this week.

Who has had success with OnlyFans during this pandemic?

funnylike

I’ve worked in the agency side for 3 years now, starting as an associate to being a manager for paid social. After much reflection, I no longer feel the fire of wanting to continue in the agency side and started applying for jobs to move toward client side. So far I haven’t had any hints nor any leads to get into the door. The most lead I get are for agency calls, which I continue to decline. I haven’t had any luck on LinkedIn as well. How do I make the transition out of agency into client side?

like

Hi! I think I need 12 likes to chat. Please help me out too ◡̈

like

At SaT, What was the hike % at 'Senior' level last year for the rating of 3 ?

likehelpful

How long did you guys keep the cone on post neuter? Then when can I get him groomed. He stinky

like

I have two colors of a coat. Which do you all prefer? Like options below to vote. And if you have any comments, feel free to post.

I'm working with EPAM, 7.8 yoe as dot net full stack with ctc 26LPA. Fed up with my current tech stack now project want on call support over weekend. no one knows project. No one is supporting. Thinking abt current situation I'm bit worrying. Somehow given interview in EY and selected. HR ask me CTC, NP and expected CTC. I told 20% and 60 days NP. She said, will check with business and get back to me. It's been 3 days no response, when I called her today she didn't pick. Is it normal in EY?

like

Shall we organize a gathering next week?

like

What are the core differences between UX/API and Growth/Enterprise PM roles? (FAANG)

like

If interested DM me

Post Photo

What’s the salary expectation for a senior art director position at a large agency in Chicago?

likehelpful
like

Would you rather have better work life balance at a lower salary or better pay with longer hours?

like

I have completed the interview process with Accenture for Procurement Management Role and now awaiting for HR to take on compensation discussion.
I have 4 years of experience in Procurement and Sourcing. My current CTC is 4.3LPA.
Education : MBA in Supply Chain.
My ask was 40% hike on current CTC.

Any suggestions or advise?

like

Additional Posts in Excel Genius

Are there any great market sizing and business case spreadsheet templates that you guys have come across? I came across this SaaS valuation template in this bowl which was super helpful, but nothing for sizing or business casing. Appreciate any help.

likehelpful

I did this manually but would there be a way to pivot the dates (day/month) in the rows years in columns with raw data being (date/month/year) needed a visual of same day different year

Post Photo

What are your favorite excel macros to make you more efficient at work?

like

Does anyone here use ASAP Utilites as an Excel add-in? This thing is amazing.

like

Dear auditors, please explain how you pick samples for substantative testing.

like

How would you create this formula based on picture. In cell C6 I want to return a value ; if (B6 is in column G, and C4 is in column H, then return (formula). Thank you

Post Photo
like

Do you still use index match? Is it (Always) Possible to switch to xlookup?

like

Help, simple task can’t figure it or. List of user names in one sheet compare to another list on another sheet and highlight duplicates.

like

Really wish my clients didn’t use Google Sheets. This sucks.

like

Hi,
I am trying to match in a specific row of a different sheet, although I want to use a calculated row value to specify the array to look in, see the photo for the logic I am trying to do… any ideas on how to get this to work?

Post Photo
like

Need some help. Is there a fax function (Filter) so that if I want my subtotals to show a number but nothing below it to report. One expansion of a row the subtotals show but no numbers below it.

like

Anyone use power query before? My goal is I have multiple look up values in one cell (separated by comma) and I need it to look up those values and return values from another table which also may have multiple values (separated by comma) anyone know how to do that? I played around with power query today but it wasn’t giving me what I wanted

like

I want to learn more about excel. Are there courses that you would recommend?

like

There has to be a way to restrict access on a worksheet so that only specific cells can be edited.

No matter how many times I’ve tried to automate and simplify generating reports to just inputting data into specific highlighted boxes, someone just have to try to edit a completely different cell then asking why the worksheet is “broken.”

likefunny

Does anybody have a good resource that would let me link data from QuickBooks Desktop to Excel?

like

I’ve heard of power query/pivot multiple times but what are their advantages compared to traditional excel? Anyone can share some examples?

like

My Goal: I have a drop down menu in a cell. I want it to sort conditionally based on what I select. Is that possible?
Ex: If the drop down ="done" it falls to the bottom of the list.

like

At my old firms when I would be using excel and modeling using the font schemes (blue, green, black for hardcode, link, and formula) I could use the “control shift ;” to shift through the schemes. My new firm doesn’t do this. Does anyone know why the shortcut would be different and how I can add it back?

like

I have a large spreadsheet with lots of dates, how can I identify classification based on the dates below? I.e. if your hire date was in between these parameters, it flags you. nested IFAND?

Post Photo
like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Send download link to your phone

OR

Scan your QR code to download
Fishbowl app on your mobile

By continuing you agree to Terms of Use and Privacy Policy.

Messaging rates may apply

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal