• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

This has never happened to me - VlookUp or V-Flop

Hi all:


Attached is a Sample Workbook consisting of two sheets (1) Report Agenda and (2) Report Results. The Report Agenda is my source and Report Results is my reference. I have a field in Report Agenda (1) called “Tech” which I am trying to “VlookUp” into Report Results (2). But it is a V-Flop – no results only #N/A. What is the issue? I have been using VlookUp for years and this one stuns me. Your feedback would be much appreciated. Thanks


frank
 

Attachments

bosco_yip

Excel Ninja
Try.................

1] In "Report Results" sheet A2, formula change from this >>
=MID(E2,MIN(FIND({0,1,2,3,4}+{0;5},E2&1/17)),4)

Into this >> ( add "0+" in front of MID..... )
=0+MID(E2,MIN(FIND({0,1,2,3,4}+{0;5},E2&1/17)),4)

Then, formula copied down

2] In "Report Agenda" sheet D2, formula change from this >>
=-VLOOKUP([@Tech], 'Report Results'!$A$1:$E$40,3,0)

Into this >> ( Omit "-" in front of VLOOKUP and add IFERROR function for error trap )
=IFERROR(VLOOKUP([@Tech], 'Report Results'!$A$1:$E$40,3,0),"")

Then, formula copied down

Regards
Bosco
 
Bosco:

Thanks a lot. One quick question, in the "Report Results" sheet A2, can the formula (=0+MID(E2,MIN(FIND({0,1,2,3,4}+{0;5},E2&1/17)),4) ) be changed NOT to return #value! if Column E contained a blank? See Attached for what I mean.

Frank
 

Attachments

bosco_yip

Excel Ninja
Then, add a If function in checking blank purpose, please see:

=IF(E2="","",0+MID(E2,MIN(FIND({0,1,2,3,4}+{0;5},E2&1/17)),4))

Regards
Bosco
 
Last edited:
I would like to change the formula in A2 in the Report Results spreadsheet, to say "if the first characters (from left to right) in E2 is not "https" or is "", then "". I tried to use the right operator with this formula, but it didn't work. What am I missing? See attachment row 41. I used this:

=If(OR Right (E2,5) <> “https), 0+MID(E41,MIN(FIND({0,1,2,3,4}+{0;5},E41&1/17)),4)
 

Attachments

Hi Bosco: Quick question, if I may. The following formula looks at a cell and determines what 4 numbers are, as follows:

Code: =IF(LEFT(L2,5)="https",0+MID(L2,MIN(FIND({0,1,2,3,4}+{0;5},L2&1/17)),4),"")
Cell L2: https://fluxer.net/request_portal/txmrs/8903/edit
Results: 8903

In the following – (same formula):

Code:
=IF(LEFT(L3,5)="https",0+MID(L3,MIN(FIND({0,1,2,3,4}+{0;5},L3&1/17)),4),"")
Cell L3: https:// fluxer.net/:x:/r/sites/DeploymentDocuments/Shared%20Documents/IIO%20-%20DEC/2019/DEC_NDC%20-%20DEC_New%20VIP%20Hubs_09.03.2019/TRF_CPC_VPN_HUB.xlsx?d=we0932f321d6c7cd29x1e52c8s97s13bc&csf=1&e=KzEuWZ
Results: #VALUE!

How do I change the formula so that #VALUE! is a “” (blank)

Thanks for your support and patience.

frank
 

bosco_yip

Excel Ninja
To remove "#VALUE!" and return blank, In cell L2 copied down :

=IF(LEFT(L2,5)="https",IFERROR(0+MID(L2,MIN(FIND({0,1,2,3,4}+{0;5},L2&1/17)),4),""),"")

Regards
Bosco
 
Top