Return Cell Info From Other Sheet Using ISERROR/INDIRECT/ADDRESS/MATCH

[sd2173]

Good afternoon all, thank you in advance for taking the time to assist.

I am currently working on a formula to do the following:

*See if cell B2 of current sheet matches any cell in column B of another sheet, 'Scores'.
*If matched, return the cell value in column 4 of other sheet, 'Scores' to active sheet where I am putting and dragging down formula.

Here is what I have for a fomula, my problem is that the 4 at the end of the fomula returns column 4 of active sheet and not 'Scores'. I have tried Scores!4 as a stab in the dark but this does not seem to work. Any help appreciated!

=IF(ISERROR(MATCH(B2,Scores!B:B)),"",INDIRECT(ADDRESS(MATCH(B2,Scores!B:B,0),4)))

Regards,
SD

 

[tweedle]

=IF(ISERROR(MATCH(B2,Scores!B:B)),"",INDIRECT("Scores!"&ADDRESS(MATCH(B2,Scores!B:B,0),4)))

A vlookup would pull the same.
=IF(ISERR(VLOOKUP(B2,Scores!B:D,3)),"",VLOOKUP(B2,Scores!B:D,3))

 

[jim may]

Or,, (if you are xl2007+)

[TABLE="width: 54"]
<tbody>[TR]
[TD="class: xl63, width: 72, bgcolor: transparent"]=IFERROR(INDIRECT("Scores!"&ADDRESS(MATCH(B2,Scores!B:B,0),4)),"")
[/TD]
[/TR]
</tbody>[/TABLE]

 

[sd2173]

Works like a dream, although for some reason it is leaving the 6th cell down blank when I paste, e.g.

[TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]650658
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: right"]236
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]672095
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]228
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]608756
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]249
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]610110
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]230
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]676909
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]231
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]606182
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]655891
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]233
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]659259
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]234
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]662582
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]231
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]611653
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]235
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]610163
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]232
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]676874
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]232
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]619855
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]232
[/TD]
[/TR]
</tbody>[/TABLE]

I checked the Scores worksheet and the 6 digit numbers match and it has a value in the cell, I actually pasted the list on my active from the scores page so I know they match it just skips over placing the score value for 606182.

 

[barry houdini]

Which formula did you use? If you use MATCH function you would normally have 0 as 3rd argument.....or use VLOOKUP as per tweedle's suggestion

=IFERROR(VLOOKUP(B2,Scores!B:D,3,0),"")

 

[tweedle]

Any possibility of a space in either of the cells being compared?

Can also check the MATCH(B6,Scores!B:B) by itself

 

[sd2173]

I used: =IF(ISERROR(MATCH(B2,Scores!B:B)),"",INDIRECT("Scores!"&ADDRESS(MATCH(B2,Scores!B:B,0),4))) since I am running older Excel. I went back and on the iserror(match) added a ,0 after the B:B and it looks like that did the trick. I'm not saavy enough to understand why it picked most all to work and that one to not but thanks a ton for all of your help! Greatly appreciated!

SD

 

[barry houdini]

If you omit the third argument in MATCH then it defaults to 1.....which means your Scores!B:B values need to be sorted ascending, so if they aren't then you can get odd results, e.g. MATCH may give you #N/A even if the lookup value exists, so the blank was coming from your "" when MATCH returned an error.

Using INDIRECT is usually not advisable for a number of reasons:

1: it's volatile
2: You need to "hardcode" the sheet name

In Excel 2003 or earlier versions I'd suggest using this version:

=IF(ISNA(MATCH(B2,Scores!B:B,0)),"",INDEX(Scores!D:D,MATCH(B2,Scores!B:B,0)))