INDEX MATCH with multiple columns

**Stuepef** · 10-02-2023, 01:08 PM

I have a spreadsheet that has a list of companies and various pieces of information about each company. I am trying to write a formula that when I type the company name in I1, employee population, headquarters, revenue and specialty populate. INDEX MATCH is great if the companies were in ONE column, but the company name could be in any of the first 4 columns, which is where I need help.

If possible, I am trying to avoid formulas that use entire columns (ie E:E) and any 365 formulas.

Attached is the workbook, but here is a photo as well:

Untitled 2.png

**Pete_UK** · 10-02-2023, 01:29 PM

Use cell J1 as a helper cell, with this formula:

=IFERROR(MATCH($I$1,$A$2:$A$17,0),IFERROR(MATCH($I$1,$B$2:$B$17,0),IFERROR(MATCH($I$1,$C$2:$C$17,0),IFERROR(MATCH($I$1,$D$2:$D$17,0),""))))

It will find the first occurrence of the company in the appropriate column. Then you can use this formula in cell I2:

=IF($J$1="","",INDEX($F$2:$F$17,$J$1+ROWS($1:1)-1))

Copy this down to I5.

Hope this helps.

Pete

**TMS** · 10-02-2023, 01:33 PM

Try:

Formula:

Please Login or Register  to view this content.

**Pete_UK** · 10-02-2023, 01:38 PM

I'm sure it said XL2013 in the OP's profile when I answered, which explained why he wanted to avoid any 365 formulas.

Pete

**protonLeah** · 10-02-2023, 01:52 PM

Please Login or Register  to view this content.

edit (forgot):
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

**TMS** · 10-02-2023, 02:12 PM

@Pete: just saw profile. Missed the "don't use 365".

**John Vergara** · 10-02-2023, 02:28 PM

Hi everyone!

One option could be (Without CSE) :

PHP Code:


=INDEX(F$2:F$17,AGGREGATE(15,6,ROW(A$2:D$17)-ROW(A$1)/(A$2:D$17=I$1),ROWS(K$2:K2)))

Blessings!

**HansDouwe** · 10-03-2023, 01:03 AM

Or empty all expected results and try this formula:

Formula:

Please Login or Register  to view this content.

**TMS** · 10-03-2023, 01:14 AM

@Hans:

If possible, I am trying to avoid formulas that use entire columns (ie E:E) and any 365 formulas.

I made this mistake too.

**HansDouwe** · 10-03-2023, 01:40 AM

@Trevor, I have read it.
For that reason, I avoided the functions in my formula that only work in Excel 365

My formula is running in Excel 2021 also.

**TMS** · 10-03-2023, 03:42 AM

Ah, maybe so. We’ll see.

**AliGW** · 10-03-2023, 03:46 AM

If possible, I am trying to avoid formulas that use entire columns (ie E:E) and any 365 formulas.

Then you should change your profile setting to whichever version you need it to run on. Members use your profile when creating solutions - as you can see, it's caused unnecessary confusion here.

**Czeslaw** · 10-03-2023, 06:52 AM

Formula

Formula:

Please Login or Register  to view this content.

INDEX MATCH with multiple columns

LinkBack

Thread Tools

Rate This Thread

Display

INDEX MATCH with multiple columns

Re: INDEX MATCH with multiple columns

Re: INDEX MATCH with multiple columns

Re: INDEX MATCH with multiple columns

Re: INDEX MATCH with multiple columns

Re: INDEX MATCH with multiple columns

Re: INDEX MATCH with multiple columns

Re: INDEX MATCH with multiple columns

Re: INDEX MATCH with multiple columns

Re: INDEX MATCH with multiple columns

Re: INDEX MATCH with multiple columns

Re: INDEX MATCH with multiple columns

Re: INDEX MATCH with multiple columns

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Index/Match multiple criteria but searching for matches in multiple columns

[SOLVED] Index match to sum multiple match columns

Match/Index Multiple Columns

INDEX MATCH MATCH multiple columns with same heading

[SOLVED] Index / Match across multiple columns

Index Match multiple columns

[SOLVED] Index/Match across multiple columns

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions