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 columnAfter that you just pull it by the concatenated ID (e.g., value x1, value x2, etc.)

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

## Use index match... always better than vlookup

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

## Elaborate?

{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))),"")}

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

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

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

EY Marriott code?

Shall we organize a gathering next week?

## Additional Posts in Excel Genius

### New to Fishbowl?

unlock all discussions on Fishbowl.
That was just a preview…
• 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

Share

#### Embed this post

Copy and paste embed code on your site

Preview

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

OR

Messaging rates may apply