Search for number on one sheet, return ref number on a different sheet

[dwrowe001]

Hi,
Need help again with another formula.. this one is kinda complicated..

I have SHEET A Below:

Ref numbers in column A, 1 thru 75
numbers to be searched in columns B thru BT, every other row,
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]
ALL THE WAY TO BT
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]45
[/TD]
[TD]34
[/TD]
[TD]55
[/TD]
[TD]50
[/TD]
[TD]45
[/TD]
[TD]23
[/TD]
[TD]19
[/TD]
[TD]22
[/TD]
[TD]67
[/TD]
[TD]89
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]56
[/TD]
[TD]23
[/TD]
[TD]28
[/TD]
[TD]45
[/TD]
[TD]51
[/TD]
[TD]52
[/TD]
[TD]77
[/TD]
[TD]61
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]4
[/TD]
[TD]75
[/TD]
[TD]88
[/TD]
[TD]73
[/TD]
[TD]24
[/TD]
[TD]30
[/TD]
[TD]45
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]5
[/TD]
[TD]94
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need a formula which will be on SHEET B, to search the numbers in Columns B thru BT, every other row, 1 thru X ( I have Ref numbers 1 thru 75).. then return the Ref number from Column A in the row where the search number was found.

So say I was looking for the number 45, the formula should return Ref numbers 1,1,2,4... If possible I would like the returned numbers to appear in one Cell on a different sheet (SHEET B) separated by a comma..

Thank you for the help!!

 

[mart37]

Sorry not in one cell but more.
A1: 45
B1: =IFERROR(INDEX(SheetA!$B$1:$BT$149,SUMPRODUCT((LARGE(($A1=Sheet1$B$1:$BT$149)*ROW($1:$149),COLUMN()-1)))),"")

Copy this formula to the right.

 

[dwrowe001]

Sorry not in one cell but more.
A1: 45
B1: =IFERROR(INDEX(SheetA!$B$1:$BT$149,SUMPRODUCT((LARGE(($A1=Sheet1$B$1:$BT$149)*ROW($1:$149),COLUMN()-1)))),"")

Copy this formula to the right.

Hi Mart37,
thank you for replying.. I can't seem to get it to work?? I'm probably doing something wrong?

 

[mart37]

Sorry, two mistakes!

=IFERROR(INDEX(SheetA!$B$1:$B$149,SUMPRODUCT((LARGE(($A1=SheetA!$B$1:$BT$149)*ROW($1:$149),COLUMN()-1)))),"")

 

[dwrowe001]

Sorry, two mistakes!

=IFERROR(INDEX(SheetA!$B$1:$B$149,SUMPRODUCT((LARGE(($A1=SheetA!$B$1:$BT$149)*ROW($1:$149),COLUMN()-1)))),"")

Ok... almost got it, but there still is something wrong.. when I tried to put it into the cell I got a popup window asking me to update values in a different location.. FYI, I copied the sheet that I'm putting in from a different spreadsheet... looks like it wants to update values in that location?? confusing.

 

[dwrowe001]

Ok... almost got it, but there still is something wrong.. when I tried to put it into the cell I got a popup window asking me to update values in a different location.. FYI, I copied the sheet that I'm putting in from a different spreadsheet... looks like it wants to update values in that location?? confusing.

I thought it was going to work, but I was wrong.. not sure what is wrong or if I'm doing something wrong.. I tried several different configurations, variations etc.. nothing.

 

[mart37]

Have you change the reference to your sheet correctly?
Sheetname!$B$1:$BT$149 or with a space in the name: 'Sheet name'!$B$1:$BT$149

Are the rownumbers equail?
$B$1:$BT$149 changed in $B$1:$BT$200 then row($1:$200) --> 200 is number of refnumbers
$B$1:$BT$149 changed in $B$2:$BT$150 then row($1:$149)

 

[dwrowe001]

Have you change the reference to your sheet correctly?
Sheetname!$B$1:$BT$149 or with a space in the name: 'Sheet name'!$B$1:$BT$149

Are the rownumbers equail?
$B$1:$BT$149 changed in $B$1:$BT$200 then row($1:$200) --> 200 is number of refnumbers
$B$1:$BT$149 changed in $B$2:$BT$150 then row($1:$149)

I'm a little confused what I'm supposed to change to what?? I'm a EXCEL neophyte, not very savy. I can handle small easy formulas like SUM(A1:A3), but when it comes to the bigger, longer ones with strange functions like Column, row, offset etc... I get easily confused.

I have changed the references on the formula you provided to match what I have in my sheets, several different ways to correspond to the data in my sheets but it still doesn't return what It should, numbers don't match..

One strange thing that keeps occurring, when I change the values in the formula and hit return a window opens up, asking me to update values in the sheet, like it wants me to click on a file name to update values.. I did copy paste the sheet Im working in from another EXCEL spreadsheet.. so it appears it's trying to update values in that other spreadsheet... confusing...

I don't mean to be pain the rear, I really appreciate you trying to help me.
Thank you
Dave

 

[mart37]

Use copy - paste as values.
Or remove all links in the formulas.
Otherwise there are left links to the workbook copied from!

 

[dwrowe001]

Use copy - paste as values.
Or remove all links in the formulas.
Otherwise there are left links to the workbook copied from!

I've checked for any links, not sure where to check but best as I can tell there are no links visible...
When I try to paste your formula into the cell, and after I make the changes to match my sheets, I hit enter and thats when the window opens up wanting me to update values... I wish I could screen capture that part... I click on the spreadsheet name and click ok... below is what happens to the formula after that:
=IFERROR(INDEX('[Last5Ball1]Last 5 Ball 1'!$M4:$BT$178,SUMPRODUCT((LARGE(($A1='[Last5Ball1]Last 5 Ball 1'!$M$4:$BT$178)*ROW(1:178),COLUMN()-1)))),"")

Dave