VBA search for 2 cells from Sheet1 on Sheet2 & return another cell from same row to Sheet1

I was thinking of a double VBA VLOOKUP, or a .range.find method or a double conditional If for that 2 cells on Sheet1 to be found on Sheet2 and return another cell from the specific row from Sheet2 back on a specific cell on Sheet1.

Example as below:


1.png
Sheet1 where the yellow cell is the output which needs to be copied from Sheet2 (as picture below)


2.png
Sheet2


The Code should search for cells A2(ASFSAF) and D2(Female), in this example from Sheet1 on Sheet2 and if both are found and there is a match should get the C cell (Offset) from Sheet2 back on Sheet1 in C2, in this example should return back to Sheet1 the value of 31 in cell C2.

Thank you in advance!
All the best!
Gordon

Hi and welcome
Some suggestions you may fund useful:

1. Our answerers are looking to "help" more often than they are looking to "do an entire project for you for free".
2. Jump into this project yourself, when you get stuck on ONE specific hurdle, post that ONE item as a forum question.
3. Show us what you've tried in an attached sample workbook and how it's not working for you. When you're doing the work yourself, just writing out a complete example question in a forum often points you to solution before you even finish posting.
4. If you can't make any headway at all and no help is forthcoming in the free forum(s), we do have a Commercial Services forum where you can pay for assistance directly from our EF gurus."

Tried this (please see below), but it's rather wrong as I'm telling it exactly where to look on Sheet2, and it also tells exactly what cell to grab from Sheet2, rather than getting the Offset from Column C on the row were the matches were found, so it's wrong, I am just 'cheating', even though it fills the yellow cell on Sheet1 with the right value of 31(in this example).

The Code has to search for cells A2 and D2 from Sheet1 on a big range of Cells on Sheet2 such as A1:A999 and D1:D999, and look for both of them to match. Maybe the range.find with a For loop or the double vlookup in vba should be better and couple them with offset cell to get the adjacent cell from the C column from Sheet2 back on Sheet1 in the C2 cell?

Please Login or Register  to view this content.

Any help would be greatly appreciated, thanks !

This is what I've tried so far, but it's just cheating, as I know where to 'get' the data from, and the VLOOKUP is wrong as well (please see E2), as if column D on Sheet2 gets modified it still gets the value with 1 correct column (A), even if it shouldn't. I still prefer the VBA, but I'm puzzled, any help would be more than appreciated.

I think the way to go is with a For loop and with range find, such as doing a for loop to go through columns A & D Sheet2, and when both of the values from A2 and D2 match values on columns A&D sheet2, then copy the offset from column C from Sheet2 on C2 cell in Sheet1. (Please see first post on how it should be like)


Thanks

Originally Posted by jolivanes

Easy to change if required.
Change references if and where required.

Please Login or Register  to view this content.

Hello, and thank you for your reply it is appreciated, works like a charm.

Now, if I want to customize it a little. Say that on Sheet1 I have cells W3 and AE3 that have to be checked on Sheet2 with all the values from columns A & M and when it has the match such as W3 and AE3 matching both values on columns A & M from Sheet2 to return the offset from the same row (but on column O from Sheet2) to cell AF3. Would that be possible?

This is what I've tried:

Please Login or Register  to view this content.

For example it should take from Sheet1 W3(Porsche) and AE3(Diesel) and look for them on every row on columns A&M from Sheet2, then it should take the offset cell from column O (Sheet2), in this case the price in $ which would be 110000 and copy it in cell AF3 in Sheet1.


PS:The data from the Sheets serves just as an example as it was made up.

Thank you in advance!