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

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


<tbody>

</tbody>

 

[sd2173]

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

650658	236
672095	228
608756	249
610110	230
676909	231
606182
655891	233
659259	234
662582	231
611653	235
610163	232
676874	232
619855	232

<tbody>
</tbody>

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