Use of vlookup ignoring hidden data using if isna

[Malik Usman]

Hello ,
i am facing a problem while comparing two sheets (one sheet having hidden rows).
Sheet 1
cell A180 contains the lookup value

Sheet 2
Column A is the table array ( consisting of hidden arrows )
Column A is the same column from which the result is required .
i am entering the formula in B180 Sheet 1

my problem is i want to compare the data in sheet 1 – column A with the data in sheet 2 column A . but sheet two column A has hidden rows . I want to use ISNA and want to get the result in form of “Exists” & “Not Exists” form.

i have applied a lengthy formula of vlookup for ignoring hidden values in hidden rows but i only returns with the same number which is the lookup value.

=VLOOKUP(A180,IF(SUBTOTAL(3,OFFSET('[Vendor Master Data 29062017 (00000003).xlsx]VendorTable-1'!$A$2:$A$1122,ROW('[Vendor Master Data 29062017 (00000003).xlsx]VendorTable-1'!$A$2:$A$1122)-ROW('[Vendor Master Data 29062017 (00000003).xlsx]VendorTable-1'!$A$2),0,1)),'[Vendor Master Data 29062017 (00000003).xlsx]VendorTable-1'!$A$2:$A$1122,0),1,0)

By using this above mentioned formula , i am able to get the same number ( as lookup value ) . But i want to get result either in Ëxists and Not Exists form. I know it would be possible by using IF(isna) but i dont know how to combine it with this above formula ..
An urgent response will be high appreciated

this
REgards

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[sandy666]

You've Ex2016 so you can use IFNA(), like: =IFNA(your_formula,whatever_you_want_to_show_if_result_of_your_formula_is_#N/A)
or
IFERROR() for older than 2013 version of Excel

[Malik Usman]

Well, Thanks for your prompt response . as you have asked me to attach the workbook which is not possible for me as its my official data and it can be questionable act for me .
so i can attach the pictures as in what form i require my results.
thanks once again .
Regards,
Malik Usman
i couldnt found go advance so sorry for that

[sandy666]

We don't need original data.
Bottom right corner of your post: Edit Post then again bottom right corner Go Advanced and then scroll down to see Manage Attachments
or
with new post: bottom right corner: Go Advanced (the rest is the same)

goadvanced.jpg

or look here: How to...

btw, did you read post#3 ?

[Malik Usman]

Hello!! ok now i am done with GO-ADVANCE . so should i post my message in here ? or what ?

[AliGW]

All you need to do is provide a cut-down copy of the original workbook with sensitive data changed to dummy data, which requires just a little bit of effort on your part. Nobody is going to be able to help you just by looking at images.

[sandy666]

First, I repeat:
Did you read post #3 ?

[Malik Usman]

thanks for your reply

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Malik Usman]

i have read this but i dont know how to use this formula
regards

[sandy666]

maybe: =IFERROR(VLOOKUP(A180,IF(SUBTOTAL(3,OFFSET('[Vendor Master Data 29062017 (00000003).xlsx]VendorTable-1'!$A$2:$A$1122,ROW('[Vendor Master Data 29062017 (00000003).xlsx]VendorTable-1'!$A$2:$A$1122)-ROW('[Vendor Master Data 29062017 (00000003).xlsx]VendorTable-1'!$A$2),0,1)),'[Vendor Master Data 29062017 (00000003).xlsx]VendorTable-1'!$A$2:$A$1122,0),1,0),"")

[Malik Usman]

Please help him out in sensitizing data . if clicked in go advance and then clicked on attachements but it doesnot show me anything further.. can ny one plz help me so that i can sensitize data.

[sandy666]

try this:
First: Disable 3rd party progs, like AdBlock, AdGuard or something like that

To attach a file to your post,

• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

or

Click here: How to...

[Malik Usman]

i have done the same but it has attached the whole excel file so how come its dummy ?

[sandy666]

Create brand new excel file
Reflect structure of your original data with new dummy data
Use the same formula (formulas) as in your original file
save your new file: eg. example from Malik.xlsx
do the same as in post #14 with example file
done.

[Malik Usman]

Now i am attaching the dummy data. A lil bit intro to the desired results are:

Primary Sheet 1 :
It is the sheet in which i want the result to be displayed after matching with Primary Sheet 2 (attached) in form of
"Exists" and if not in primary sheet 2 so it should display "Not Exists"

i want to compare the data of Primary Sheet 1 in Column A ( i have discarded some of the data i.e if only one is matched
its sufficient for me )

Primary Sheet 2 :
This sheet is to be compared (column A) data . and the main problem is this sheet contains hidden rows which are to be
ignored or avoided ( i.e i dont want the results from the hidden data to be included in the formula or results)

Note: I've succeeded in displaying the exact match (ignoring the hidden data in Primary Sheet 2 ) but it only displays the exact Vendor Number
which is not the desired result> i want to be the result in form "Exists" & "Not Exists" form in primary sheet 1 column A.

Regards.

[sandy666]

First : you didn't change path to second file
Second:
try this in B2:

Please Login or Register  to view this content.

with Control+Shift+Enter and drag down

[avk]

In primary sheet1 in "B2"

Please Login or Register  to view this content.

[Malik Usman]

Thank avk .. i considered your point and marked it with myself .. But once the problem is solved i would act upon it .
Secondly, I wanted to ask that have u incorporated the fact the the secondary sheet contains some of the hidden rows and i think if i apply your formula on that so it would give take the hidden data as well in consideration and return the answer accordingly which i dont want to be like that ..

[Malik Usman]

THanks for your kind response . as its saturday . i can confirm it on monday to you .
Thanks once again.

[sandy666]

Use @ [person_screenname] because nobody knows to who you are talking

[Malik Usman]

@[Sandy666]
Hello Good morning !
I applied your formula (post # 12 ) but i gives me a blank result nothing showed.
I have a query and reservation on your formula(post # 18 ) i cannot and do not want to unhide my data in primary sheet 1 because its also filtered if you see in attached file (post 17) . You've asked me to apply your formula in B2 which is not possible for me because i have already applied filters and my whole working would be vanished and disturbed if i unfliter it for apply your formula .
Please help .!!!!
Regards.

[sandy666]

@Malik,

so add formula into first visible cell in B column
and confirm with control+shift+enter
or
clear filter, unhide rows, add formula, and hide rows, add filter again

or

=IFERROR(VLOOKUP(A180,IF(SUBTOTAL(3,OFFSET('[Vendor Master Data 29062017 (00000003).xlsx]VendorTable-1'!$A$2:$A$1122,ROW('[Vendor Master Data 29062017 (00000003).xlsx]VendorTable-1'!$A$2:$A$1122)-ROW('[Vendor Master Data 29062017 (00000003).xlsx]VendorTable-1'!$A$2),0,1)),'[Vendor Master Data 29062017 (00000003).xlsx]VendorTable-1'!$A$2:$A$1122,0),1,0),"Not Found") or whatever you want. Maybe with control+shift+enter but I can't check this because your example files doesn't work

[Malik Usman]

@[Sandy666]

Thanks for your cooperation . I am writing in good terms and want to tell you that the your formula has worked
and delivered the results but if you see the files that i am attaching it has given me the "Not Exists " result
but it is giving me the Vendor Number in second column ( Primary sheet 1 ) which i found in the form of "Exists" t
term.
Note : You have to open both sheets than it would show you the above mentioned results in Primary Sheet 1 (column B)


Thanks alot ..
Regards.

[sandy666]

What you want to return "if exist" ?

or
if formula return correct vendor i want to return "blablabla"
else if formula doesn't find vendor or produce error return should be "ungabunga"

if you want return Exist or Not Exist:
=IFERROR(IF(VLOOKUP(A180,IF(SUBTOTAL(3,OFFSET('[sheet2.xlsx]VendorTable-1'!$A$2:$A$1122,ROW('[sheet2.xlsx]VendorTable-1'!$A$2:$A$1122)-ROW('[sheet2.xlsx]VendorTable-1'!$A$2),0,1)),'[sheet2.xlsx]VendorTable-1'!$A$2:$A$1122,0),1,0)<>"","Exist"),"Not Exists")

you can use whatever you want
Found / Not Found
Available / Not Available
Exist / Not Exist
Found / Lost
.....
whatever

or you wanted to show it works, only ?


btw.
not @[sandy] but @sandy. [ ] was an option

[Malik Usman]

@Sandy 666..
Thanks alot for your recent formula post # 26 . Atlast it solved my problem..
I feel highly obliged and impressed by your skills at the same time ..
My GOD bless you.
Regards...

[sandy666]

You are welcome.

If that takes care of your original question, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you) then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED. Thanks.

[Malik Usman]

@sandy.. i have already done the both..
Regards.