0

I'm looking for a way to insert a column based on two criteria, as illustrated below. I have a main table with one row per company, and I want to add a column to this with the city names. However, the lookup table has two rows for some companies - one for "small" and one for "large". I'm only interested in retrieving the cities for companies that have size value "small".

enter image description here

I know that I can achieve this with =SUMIFS if the content of the column was a number instead of text. However, with the cities column consisting of text, I don't know how to proceed. I'd ideally like a solution where I don't have to use a helper column.

Edit: this is just an example of my data. I have hundreds of rows,the duplicate answer suggested uses INDEX/MATCH which requires me to give the exact cell location of each condition. This is not the case in my data.

Kara W
  • 143
  • 5
  • 12

3 Answers3

0

There are a few solutions that I usually use for these tasks. They're not elegant i.e. not a 2-criteria look-up per se, but they get the job done.

Going by your data structure, you have these choices:

  1. Sort your lookup table by size-company, with size in descending order. Thereafter, it's a straightforward vlookup since your big companies are seggregated from small ones.

  2. Build a new key consisting of company-size i.e. CONCAT(company,size) and do the vlookup based on this key.

kerwei
  • 1,822
  • 1
  • 13
  • 22
0

It's not possible with VLOOKUP. Look my solution in the picture using a array formula.

Solution using array formulas

Formula in F2: =INDEX($C$1:$C$6;SUM(IF(E2=$A$2:$A$6;1)*IF($B$2:$B$6="small";1)*ROW($C$2:$C$6));1)

Ps: don't forget to confirm the formula with Ctrl+Shift+Enter.

Michel Excel
  • 25
  • 5
0

Multi-column lookups are certianly possible but not using VLOOKUP. You'll need to use INDEX and MATCH. This becomes pretty complex as it combines array formulas with boolean logic. Here's a nice explanation.

https://exceljet.net/formula/index-and-match-with-multiple-criteria

For your example, assuming Desired Result Company is in column I.

=INDEX($F$4:$F$5,MATCH(1,(D4:D5=I4)*(E4:E5="small"),0))
Mike
  • 164
  • 1
  • 4