Excel 2007 : Vlookup and/or Iserror Funtion Question

[rbubba117]

Hello,

Based on an ID system, I used vlookup to get dollar amounts from four different worksheets because they're categorized. Now my main worksheet has the ID column and four columns representing each category. Since the first column represents the ID numbers, each ID row only has one correct value, while the other three values in each row are "#N/A".

Somehow, I would like to write a function that searches each ID row and only returns a value if it is not "#N/A".

Can somebody please help me?

Thank you,
Ract

[benishiryo]

hi rbubba117, welcome to the forum. i dont know how your VLOOKUP formula looks like, so i'll just bold the VLOOKUP formula u are supposed to insert:
=if(isna(VLOOKUP),"",VLOOKUP)

upload your file if this doesnt help

[rbubba117]

Thank you for the quick response, but I am still confused.

I have attached my file to show what exactly I am trying to do.

Any additional help would be greatly appreciated!

rbubba117

[vlady]

maybe like this.(iserror)vlookupxl2003-rbu.xls

[benishiryo]

are your worksheets really named Cat 1 to Cat 4? if so, u can use this 1 formula at B2 & paste it to the rest:

=IF(ISNA(VLOOKUP($A2,INDIRECT("'Cat "&COLUMN(A2)&"'!$A$1:$B$3"),2,FALSE)),"",VLOOKUP($A2,INDIRECT("'Cat "&COLUMN(A2)&"'!$A$1:$B$3"),2,FALSE))

if not, u can use this at B2 & paste to the rest but u need to find & replace Cat 1 to Cat 2/3/4 for each different columns:

=IF(ISNA(VLOOKUP($A2,'Cat 1'!$A$1:$B$3,2,FALSE)),"",VLOOKUP($A2,'Cat 1'!$A$1:$B$3,2,FALSE))

ISERROR as mentioned from vlady works as well. it's just a personal preference of mine to only make sure other errors other than #N/A gets highlighted for VLOOKUP

[rbubba117]

benishiryo & vlady,

Thank you both for your help! I have given you both great ratings.

Thanks again,
rbubba117

[benishiryo]

glad to help, rbubba~ =)
could u go to the thread tools -> Mark this thread as Solved

thanks for the rep too!