VLOOKUP Error Help Please.

Lewie

New Member
Joined
Jul 25, 2015
Messages
14
This is the code I have on Worksheet "Iso Register"

[TABLE="class: grid, width: 332"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD][/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]Rev[/TD]
[TD][/TD]
[TD]Admin Lock[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD]FALSE

[/TD]
[/TR]
</tbody>[/TABLE]

What I'm trying to do is report a Y or N in cell S4.

A Y would report if for example Revision is 0 and Admin Lock is True. But If Revision is 0 and is Admin False report N.

=IF(ISBLANK($A4),"",IF($T4="Y","",IF(ISERROR(VLOOKUP($E4,Rev_Criteria,1,FALSE)),"Y",IF(VLOOKUP($J4,Rev_Criteria,2,FALSE)=$J4,"Y","N"))))



Rev_Criteria refers to =OFFSET('Rev Criteria'!$A$1,0,0,COUNTA('Rev Criteria'!$A:$A),COUNTA('Rev Criteria'!$1:$1))


Rev Criteria - Worksheet

[TABLE="class: grid, width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="width: 64"]Revision[/TD]
[TD="width: 64"]Admin Lock[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]13[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD]D[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD]E[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD]F[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD]G[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD]H[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD]I[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD]J[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD][/TD]
[TD]K[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD]L[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD]M[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD]N[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

If you need any more information let me know. I'm desperate to solve this.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I'm having trouble getting to work in my spreadsheet. Is it because the Rev is from another lookup?

Edit: I'm only getting a N
 
Last edited:
Upvote 0
I'm having trouble getting to work in my spreadsheet. Is it because the Rev is from another lookup?

Edit: I'm only getting a N

In order to validate an array formula, you need to simultaneously use the 3 keys : Ctrl+Shift+Enter ... instead of the Enter key ...

HTH
 
Upvote 0
Thanks very much for the help. I have it working now.

It seems to run through the update code a lot slower now, but I guess its something I could put up with until I learn more about how these Vlookups are supposed to be set-up.
 
Upvote 0
Thanks very much for the help. I have it working now.

It seems to run through the update code a lot slower now, but I guess its something I could put up with until I learn more about how these Vlookups are supposed to be set-up.

In order to speed up the process, you can restrict the arrays to the exact number of lines ...

=IF(ISERROR(MATCH(E4&J4,'Rev Criteria'!A1:A30&'Rev Criteria'!B1:B30,0)),"N","Y")

HTH
 
Upvote 0
Sorry for pestering you, but how do you fill the formula down without A1:A30 & B1:B30, filling to A2:A31 & B2:B31 etc... Obviously E4&J4 will still have to go up. I have tried using a name I have created, but have had no luck. With my spread sheet update and drags in new entries from an external source it now take for ever.
 
Upvote 0
Sorry ... should have provided it right from the start ...

=IF(ISERROR(MATCH(E4&J4,'Rev Criteria'!$A$1:$A$30&'Rev Criteria'!$B$1:$B$30,0)),"N","Y")

HTH
 
Upvote 0

Forum statistics

Threads
1,223,713
Messages
6,174,043
Members
452,542
Latest member
Bricklin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top