Index & match - if cell reference is blank reference cell to the left

bbaker012

New Member
Joined
May 10, 2019
Messages
2
I have a spreadsheet comparing over 300 columns of data. For this purpose I have a sample spreadsheet with only 3 data sets. I'm attempting to Index and Match utilizing a data validation menu drop down box.

Each set of Data is located in 2 columns - the second containing a much needed modifier code. However, the plan names - which I'm matching are only located in the first column (centered by alignment + center across selection) so when I attempt to match the 2nd column (left) the reference cell is blank.

Is there a dynamic formula where I can match the reference column to the left of the blank cell containing the plan name where I can the drag across 300 data sets (2 columns each)?

I'm new to forum so if someone would be kind enough to instruct me how to include an attachment, I'm happy to attach the sample spreadsheet to reference.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
I have a spreadsheet comparing over 300 columns of data. For this purpose I have a sample spreadsheet with only 3 data sets. I'm attempting to Index and Match utilizing a data validation menu drop down box.

Each set of Data is located in 2 columns - the second containing a much needed modifier code. However, the plan names - which I'm matching are only located in the first column (centered by alignment + center across selection) so when I attempt to match the 2nd column (left) the reference cell is blank.

Is there a dynamic formula where I can match the reference column to the left of the blank cell containing the plan name where I can the drag across 300 data sets (2 columns each)?

I'm new to forum so if someone would be kind enough to instruct me how to include an attachment, I'm happy to attach the sample spreadsheet to reference.

Welcome to the Forum!

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
You have this formula:
=INDEX(SOURCE!$B9:$G9, MATCH(B$2, SOURCE!$B$2:$F$2,0))


You are telling to look for "UHC_AVXD" in row 2 (B2:F2), finds it in the first cell of the range, it then returns a 1.


Then you ask for the index of B9:G9 and you pass the 1 (result of the match formula), then the data of B9 returns to you.


But that is equivalent to putting
=B9

I suppose you want to find the match of "Primary Care Co-Pay" and "UHC_AVXD" in sheet "SOURCE" and return the intersection.


Anyway, maybe this formula will serve you for what you already have.

=INDEX(SOURCE!$B9:$G9,MATCH(IF(B$2="",A$2,B$2),IF(B$2="",SOURCE!$A$2:$F$2,SOURCE!$B$2:$F$2),0))
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top