# Modify Vlookup search formula

#### Tankstand

##### New Member
Hello all,

First time poster here.

I have a formula that searches through 4 data ranges for a players rego number and returns a value (number of 180's thrown), if the player exists in more than one data range (A dart division) I am only getting the first value the formula finds and not several added together.

Is it possible to attach workbooks on this forum?

Forgive the cross post but I have an example workbook on Ozgrid. http://www.ozgrid.com/forum/showthread.php?t=166022

In the attached cut down workbook the example is for rego number 415 Gisborne Stanley, he has thrown 1 180 in Premier B and 3 180's in Division 2 but in the 180 register the total in column N is 1 and not 4.

=IF(NOT(ISNA(VLOOKUP(A4,PremA,10,0))),VLOOKUP(A4,PremA,10,0),IF(NOT(ISNA(VLOOKUP(A4,PremB,10,0))),VLOOKUP(A4,PremB,10,0),IF(NOT(ISNA(VLOOKUP(A4,_Div1,10,0))),VLOOKUP(A4,_Div1,10,0),IF(NOT(ISNA(VLOOKUP(A4,_Div2,10,0))),VLOOKUP(A4,_Div2,10,0),0))))

Could someone educate me as to the correct formula to use?

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Do all of the tables have the same lay-out? If so, what is the range of PremA?

Do all of the tables have the same lay-out? If so, what is the range of PremA?

Hi,

All four ranges are the same (Or soon will be) A4:M122

Thanks.

Hi,

All four ranges are the same (Or soon will be) A4:M122

Thanks.

Create on the formula sheet a range (say X2:X5) which houses the relevant sheet names, that is, PremA, PremB, _Div1, and _Div2.

Now invoke say in B4...

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&\$X\$2:\$X\$5&"'!A4:A122"),A4,INDIRECT("'"&\$X\$2:\$X\$5&"'!J4:J122"),180))

where A4 is a player rego number.

Does this help?

Thank you for your help, but I am struggling with making this work.

Is there a way you can look at my example workbook? It is only 33KB in size.

Can I e-mail it to you via PM?

Thanks

Mark.

I think it's easyer to make a new sheet with all data.

Add an extra collumn for the division (table).

Then you can use a pivot table.

Combine the worksheets is possible with VBA.
(I can't help you enough with that part of the question).

With excel 2010:

Code:
``=IFERROR(VLOOKUP(A4,PremA,10,0),0)+IFERROR(VLOOKUP(A4,PremB,10,0),0)+IFERROR(VLOOKUP(A4,_Div1,10,0),0)+IFERROR(VLOOKUP(A4,_Div2,10,0),0)``

Thanks for the link.

Dart Database.xlsm

http://www.mijnbestand.nl/share.php?key=DGSFE84UWALT

I hope this is OK?

Add a new sheet. Rename this sheet as Admin.

Create the following range in A1:A5 on Admin:

<TABLE style="WIDTH: 94pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=125><COLGROUP><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4437" width=125><TBODY><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 13.2pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18 width=125>Sheets</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=18>PREMIER A TABLE</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=18>PREMIER B TABLE</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=18>DIV 1 TABLE</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=18>DIV 2 TABLE</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=18></TD></TR></TBODY></TABLE>

Select A2:A5. Name the selection as SheetList via the Name Box on the Formula Bar.

Go to the sheet named 180 REGISTER...

In N3 enter and copy down:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A4:A122"),\$A3,INDIRECT("'"&SheetList&"'!J4:J122")))

Or, as FormR suggests, sum or add the results of the VLOOKUP formulas applied to each named range.

Last edited:

Replies
3
Views
168
Replies
7
Views
445
Replies
2
Views
1K
Replies
8
Views
903
Replies
1
Views
3K

1,202,991
Messages
6,052,965
Members
444,622
Latest member
Kriszilla

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

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