# Reference multiple rows and columns

#### Stephenosn

##### Board Regular
I'm trying to change a formula I currently use that references cells in only one row. =IF(ISNA(MATCH(\$B5,June!B\$3:B\$7,0)),MAX(C\$4:C4)+1,"")

Is it possible to change the June!B\$3:B\$7 to also reference columns B through Z and rows 3 through 7?

Thanks for taking a look.

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
MATCH returns the relative position of the match within the lookup array so the lookup array must be within a single row or single column. Like June!B\$3:B\$7 or June!B\$3:Z\$3, but not June!B\$3:Z\$7.

Perhaps try

=IF(SUMPRODUCT(--(June!B3:F7=\$B6))=0,MAX(C\$4:C5)+1,"")

Could I use a Vlookup function with the match function?

PCL I'm trying out your formula but keep getting a circular dependency warning.

Where did you put it, which cell and which sheet ?
Personally I put it in sheet1 G8

I put it on a sheet labeled NamesUsed. I started in cell C5. I finally found a formula that worked. =if(sumproduct(June!\$B\$3:\$Z\$7=\$B35),"",max(C\$34:C34)+1). It looks like you had the answer the entire time. Thank you for your help.

Replies
0
Views
105
Replies
3
Views
92
Replies
1
Views
298
Replies
16
Views
527
Replies
7
Views
127

1,203,646
Messages
6,056,531
Members
444,872
Latest member
agutt

### 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.

### Which adblocker are you using?

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

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