Match two values with two values on another sheet and return value from third column

  • Hopefully a kind soul will help me out here, so this is what I want to happen -



    Lookup cell $H$2 with cell L3 on sheet 2 and find the match on sheet 1 in column E and column A.
    When the match is found enter the value from P3 into the corresponding row in column F on sheet 1.
    Repeat process for the 6 rows in sheet 2.
    Sheet 2 always has a max of 6 rows to search through, sheet 1 has a differing amount of rows each day.



    I would take a guess at this needing something like UBound which is something I have no clue about, I usually record a macro and edit it to suit my needs but this is beyond me! If anyone is able to help would
    they also be able to pop a few notes in the code to help me understand what the hell is going on!


    [ATTACH=CONFIG]73409[/ATTACH][ATTACH=CONFIG]73410[/ATTACH]

  • Re: Match two values with two values on another sheet and return value from third col


    Ah...testing. deleting any entries in both bet columns and entering a 1 or 0 in the column on sheet 2 . I guess copying some data from sheet 1 and pasting it into sheet 2 to fill the 6 rows may help. The 6 rows on sheet 2 won't always be filled, anything from 1 to 6, then I select the next slot number via a slicer and it gets filled with different data and so on. Look I'm really sorry if I'm testing your patience here, it's not intended, I guess in my mind I know how it will fit into the grand scheme of things! And I'm struggling to get that across.

  • Re: Match two values with two values on another sheet and return value from third col


    This can be done with a formula, why do you want code?


    With a formula if you change what is now 55 then those that don't match will go blank and those that do will show what's in the bet column.

  • Re: Match two values with two values on another sheet and return value from third col


    The reason for code is to stay on the same sheet to go through each slot number looking at different charts graphs stats etc and selecting bets accordingly. I was thinking that when a 1 or 0 is placed in sheet 1 it would stay there unless I re-selected that slot number, changed the bet and ran the code again as the code would ignore anything that didn't match the slot and qualifier. Am I asking the impossible here? The qualifiers are listed on sheet 2 from a dynamic list from sheet 1 (sheet 1 contains the raw data that comes in from a database) depending which slot is selected on the slicer. I select a 1 or 0 for each qualifier then run the code which finds the the match in sheet 1 and clears the bet column in sheet 2. Rinse and repeat for all the days slots. All the qualifiers with a 1 on sheet 1 then get sent to a bot through existing code. Does this help to explain more what I'm trying to do?

  • Re: Match two values with two values on another sheet and return value from third col


    The aim is to get a full column of values for bets (1's or 0's) in sheet 1 without switching back and forth to manually do this and making a mistake which is easier to make when there's 20 or 30 qualifiers. Another reason for why I have it set up like this is because there is never more than 6 qualifiers per slot which allows me to work from a one screen dashboard, no scrolling around or selecting other sheets.

  • Re: Match two values with two values on another sheet and return value from third col


    Another approach maybe

  • Re: Match two values with two values on another sheet and return value from third col


    Thanks so much for your help skywriter! What you've done is a great help to me, I'm currently working on trying to implement your code into my actual workbook which I'm finding quite tricky, it's pretty much the same set up but with extra columns here and there. This way I'll be able to understand the mechanics of the code better and maybe in the future refer to it and be able to adapt it for something else. Thanks again for the help, patience and quick replies, very kind of you.

  • Re: Match two values with two values on another sheet and return value from third col


    Kind of you to offer a different approach and I am interested in understanding this method too. At the moment it's producing runtime error 91 'Object variable or With block variable not set' on this line


    Code
    1. x = .Range("A3:A" & .Cells(.Rows.Count, 1).End(xlUp).Row).Find(b(i)).Row
  • Re: Match two values with two values on another sheet and return value from third col


    I made some notes for you, hopefully you will find them useful. :cheers:


  • Re: Match two values with two values on another sheet and return value from third col


    jolivanes - This gives 'run time error 9 subscript out of range' on this part of the last line :

    Code
    1. .Cells(x, 1).Offset(, 5) = bb(i)



    It looks like the code runs from the wrong page and the sheets are the wrong way round maybe. The manual entry is made in sheet 2 for a slot number then the code is run, repeat for each slot number til the bet column in sheet 1 is full. the slots are displayed and selected via slicer on the sheet I'll actually use. Sorry for any confusion I may have caused, there's a better attempt at explaining what I would like to happen a couple of posts back if that helps.