Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
If no match leave blank
- Thread starter puup
- Start date
AnotherHiggins
Technical User
wrap your formula in a function that will test for the NA error:
=if(isna(INDEX(Sheet3!B:B,(MATCH(Sheet2!A2,Sheet3!A:A,0)))), "", INDEX(Sheet3!B:B,(MATCH(Sheet2!A2,Sheet3!A:A,0))))
[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
=if(isna(INDEX(Sheet3!B:B,(MATCH(Sheet2!A2,Sheet3!A:A,0)))), "", INDEX(Sheet3!B:B,(MATCH(Sheet2!A2,Sheet3!A:A,0))))
[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
SkipVought
Programmer
...and to take it one step further, I just test the MATCH function, as it is a bit less overhead, and a bit cleaner...
[tt]
=if(isna(MATCH(Sheet2!A2,Sheet3!A:A,0)), "", INDEX(Sheet3!B:B,(MATCH(Sheet2!A2,Sheet3!A:A,0)))
[/tt]
Skip,
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my old subtlety...for a NUANCE!
![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
- Thread starter
- #4
AnotherHiggins
Technical User
Actually when I do this in real life, I only test a CountIf, because (I think) it takes even less overhead. But I'm not positive that's true.... I'd be interested to know which is 'faster'.
=If(CountIf(Sheet3!A:A, Sheet2!A2)>0, INDEX(Sheet3!B:B,(MATCH(Sheet2!A2,Sheet3!A:A,0))), "")
[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
=If(CountIf(Sheet3!A:A, Sheet2!A2)>0, INDEX(Sheet3!B:B,(MATCH(Sheet2!A2,Sheet3!A:A,0))), "")
[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
SkipVought
Programmer
John, In Real Life...
has a kind of ring to it!
Skip,
Just traded in my old subtlety...for a NUANCE!
AnotherHiggins
Technical User
Yeah, yeah, yeah - So I'm not "down" with the lingo of you rascally kids and your "1337 speak" ![[wink]](/data/assets/smilies/wink.gif "[wink] [wink]")
I'm sorry, Skip. ITF (in the future), I will make EAP (every attempt possible) to use as many TLAs (three-letter acronyms) AHP (as humanly possible). I'll even GSFAT (go so far as to) use ETLAs (extended three-letter acronyms) and VLTLAs (very long three-letter acronyms). ITB (is that better)?
[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
I'm sorry, Skip. ITF (in the future), I will make EAP (every attempt possible) to use as many TLAs (three-letter acronyms) AHP (as humanly possible). I'll even GSFAT (go so far as to) use ETLAs (extended three-letter acronyms) and VLTLAs (very long three-letter acronyms). ITB (is that better)?
[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
- Status
Similar threads