Help with using index match formula with a variable column

**C J W** · 10-27-2017, 03:04 PM

Hi All

I hope that some one will be able to help me with the following!

I am trying to use an index match combination to extract a list of 'members' from a data sheet, but the user has 4 options to select from which makes the column I need to match change. I have been able to identify the column and range which I need to match, but I am unable to get this range into the formula, I have tried to use indirect but I am not having much luck!

I have attached a simplified sheet to show what I am trying to achieve (and what I have so far)

Thanks

**CK76** · 10-27-2017, 03:42 PM

Personally, I'd recommend changing structure a bit. But following should work.

Change G4 formula to...
="'Data'!"&ADDRESS(5,G3)&":"&ADDRESS(25,G3)

Then C8 formula becomes...
=IFERROR(INDEX(Data!$B$5:$B$25,SMALL(IF(INDIRECT($G$4)=$C$5,ROW(INDIRECT($G$4))-4),ROW(A1))),"")

Confirmed as array (CTRL + SHIFT + ENTER).

**C J W** · 10-27-2017, 04:21 PM

Thank you! exactly what I needed

Help with using index match formula with a variable column

LinkBack

Thread Tools

Rate This Thread

Display

Help with using index match formula with a variable column

Re: Help with using index match formula with a variable column

Re: Help with using index match formula with a variable column

Thread Information

Users Browsing this Thread

Similar Threads

Using INDEX/MATCH with variable column names

Match/Index maybe? Formula to pull a cell in column A based on a match for two columns

Three Variable Match / Index (With different column headings)

[SOLVED] Converting Hlookup to Index Match with variable column index number

Variable Column Height Index/Match

How to Index and match to return the last value from variable column

Consolidate Index/Match formula into variable range

Bookmarks

Bookmarks

Posting Permissions