Modify Vlookup search formula

[Tankstand]

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?

 

[Aladin Akyurek]

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

 

[Tankstand]

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.

 

[Aladin Akyurek]

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?

 

[Tankstand]

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.

 

[Oeldere]

This is a (dutch) site where you can share files.

http://www.mijnbestand.nl/

 

[Tankstand]

Thanks for the link.

Dart Database.xlsm

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

I hope this is OK?

 

[Oeldere]

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

 

[FormR]

With excel 2010:

=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)

 

[Aladin Akyurek]

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.