+ Reply to Thread
Results 1 to 11 of 11

how can i use vlookup for cell that has 2 different variables?

  1. #1
    Forum Contributor
    Join Date
    03-10-2017
    Location
    USA
    MS-Off Ver
    office 2016
    Posts
    393

    how can i use vlookup for cell that has 2 different variables?

    I have been using Vlookup to find a row of data with this code.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    A149 has the value of '13L - 40L'... up till now it hasnt mattered what was in the cell because the data i'm looking up had the same way of writing it. But now the data i'm looking up has changed how they reference it. Instead of '13L - 40L' its now '40L - 13L'. I thought of just changing all the references around to 13L - 40L but im looking for a formula that will look for both variables no matter what order they appear because I fear it may change again in the future and I dont have control of how the exported data comes to me. I'm assuming some sort of trim left or trim right might work but i'm not experienced with that.

    attached is an exmple of what im doing and what i want to have as a result
    Attached Files Attached Files
    Last edited by kevinu; 08-20-2018 at 03:38 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how can i use vlookup for cell that has 2 different variables?

    Quote Originally Posted by kevinu View Post
    ...
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    A149 has the value of '13L - 40L'...
    Your sample file only goes down to row 7, and you have no sheet called coherence?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    03-10-2017
    Location
    USA
    MS-Off Ver
    office 2016
    Posts
    393

    Re: how can i use vlookup for cell that has 2 different variables?

    ya my example was just an example... the actual file is too large to put up. I was hoping you could make it work with the example then I'd adapt it for the real program.

    Here is the example with the correct tabs
    Attached Files Attached Files
    Last edited by kevinu; 08-20-2018 at 03:51 PM.

  4. #4
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: how can i use vlookup for cell that has 2 different variables?

    =index(b:b,aggregate(15,6,row($a$2:$a$7)/isnumber(find(left($l14,3),$a$2:$a$7))/isnumber(find(right($l14,3),$a$2:$a$7)),1))

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how can i use vlookup for cell that has 2 different variables?

    Im guessing this is what you want?
    =VLOOKUP(IF(ISERROR(MATCH($L2,$A$2:$A$7,0)),RIGHT($L2,3)&" - "&LEFT($L2,3),$L2),$A$2:$I$7,COLUMN(B1),0)
    copied across

  6. #6
    Forum Contributor
    Join Date
    03-10-2017
    Location
    USA
    MS-Off Ver
    office 2016
    Posts
    393

    Re: how can i use vlookup for cell that has 2 different variables?

    Thanks both worked great

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how can i use vlookup for cell that has 2 different variables?

    Happy to help and thanks for the feedback

  8. #8
    Forum Contributor
    Join Date
    03-10-2017
    Location
    USA
    MS-Off Ver
    office 2016
    Posts
    393

    Re: how can i use vlookup for cell that has 2 different variables?

    FDibbins, I was using your code and it worked but Excel gave me a warning saying there was a circular argument that would cause issues. Even though it worked it slowed the program down a lot. I cant seem to see a circular argument in the formula but if i erase the formula the warning goes away and the program isnt slowed down. Can you tell me if there is an issue with it? FYI I use this formula in over 7k cells so any lag in it is exponential exacerbated. The formula i ended up using is this

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    i did change the end 'column(b1),0' to 'column(b$1),0' thinking it needed to stay B1 when i dragged down but truthfully it worked either way.
    Last edited by kevinu; 08-21-2018 at 03:22 PM.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how can i use vlookup for cell that has 2 different variables?

    Found the problem - you have the data table on another sheet...
    =VLOOKUP(IF(ISERROR(MATCH($A2,Coherence!$A$1:$I$6,0)),RIGHT($A2,3)&" - "&LEFT($A2,3),$A2),Coherence!$A$1:$I$6,COLUMN(B1),0)

    The column(B1) is to create a "count" as you drag it across. Dragging it down has no affect, based on the context I used it in

    I used it to change the column number in the vlookup automatically, so it doesnt need to be input manually

    Try it in a cell on it's own, and play around with it. It us a useful technique to create sequential numbers for columns. rows(A1) does just the opposite, for rows
    Last edited by FDibbins; 08-21-2018 at 03:38 PM.

  10. #10
    Forum Contributor
    Join Date
    03-10-2017
    Location
    USA
    MS-Off Ver
    office 2016
    Posts
    393

    Re: how can i use vlookup for cell that has 2 different variables?

    awsome thanks for helping me understand the code and helping find the bug. I should have made it more clear there were different sheets used.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how can i use vlookup for cell that has 2 different variables?

    Actually, you did, in the 1st post. But the sample file had all info on 1 sheet - I should have mentioned you needed to change the ranges

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Vlookup with three variables
    By westsidebill in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-21-2018, 08:51 PM
  2. VLookup two Variables
    By domnicmifsud in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-15-2016, 09:37 AM
  3. [SOLVED] vlookup with variables
    By vio.coman in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-04-2015, 09:26 AM
  4. Replies: 5
    Last Post: 06-21-2013, 12:54 PM
  5. [SOLVED] Vlookup two variables
    By PERE in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-28-2013, 06:36 AM
  6. Replies: 9
    Last Post: 08-13-2012, 08:00 AM
  7. [SOLVED] Can I identify variables using VLOOKUP, and then pass variable to another VLOOKUP?
    By Jeff C in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-05-2012, 05:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1