Vlookup or Macro so that if the data within 4x cells on one sheet match another 4x cells on another sheet then a word is input in a cell in the first

[David117]

Vlookup / Macro required so that if the data within 4x cells (A2,B2, C2 & D2) on one sheet (TemplatesOverview) match the corresponding 4x cells (A2,B2, C2 & D2) on another sheet (TemplatesRecord) then a word is input in cell F2 in the first sheet (TemplatesOverview)

Row 1 would not be included in the check

Do not mind if it is a Vlook up or a Macro but it would have to check check row to the bottom of the available rows

 

[Joe4]

You could place a formula like this in cell F2 of your TemplatesOverview sheet:

=IF(AND(A2=TemplatesRecord!A2,TemplatesOverview!B2=TemplatesRecord!B2,TemplatesOverview!C2=TemplatesRecord!C2,TemplatesOverview!D2=TemplatesRecord!D2),"Word","")

 

[David117]

Bloody Hell Joe4! that was quick
Is there a way to adjust this so that for cell F2, if the described information matches then the information in Cell E2 of sheet TemplatesRecord will appear in Cell F2 of sheet TemplatesOverview

 

[David117]

Bloody Hell Joe4! that was quick
Is there a way to adjust this so that for cell F2, if the described information matches then the information in Cell E2 of sheet TemplatesRecord will appear in Cell F2 of sheet TemplatesOverview

Change that, I've just went over the information and noticed that the information in Sheet TemplatesRecord isnt in the same row ordering as per the previous sheet. Is there a way for this to be factored in

 

[Joe4]

Change that, I've just went over the information and noticed that the information in Sheet TemplatesRecord isnt in the same row ordering as per the previous sheet. Is there a way for this to be factored in

Well, that is a significant changes that changes the nature of the question entirely.
I would recommend using a COUNTIFS function, and count how many records on the other page match those 4 values exactly, i.e.

=IF(COUNTIFS(TemplatesRecord!A:A,TemplatesOverview!A2,TemplatesRecord!B:B,TemplatesOverview!B2,TemplatesRecord!C:C,TemplatesOverview!C2,TemplatesRecord!D:D,TemplatesOverview!D2)>0,"Word","")

 

[David117]

Cannot believe how fast you are coming back with this! Thank you
Is there a way to add on to this vlookup that instead of a predefined word appearing in F2 it will complete with whatever has been input in cell E2 of sheet TemplatesRecord ?

 

[Joe4]

Cannot believe how fast you are coming back with this! Thank you
Is there a way to add on to this vlookup that instead of a predefined word appearing in F2 it will complete with whatever has been input in cell E2 of sheet TemplatesRecord ?

There is no VLOOKUP involved. Just replace the word "Word" in the formula with the cell reference.
However, do you REALLY want it to return E2 from the TemplatesRecord sheet, or actually column E from whatever row matches (i.e. if it matches row 14, then you want to return "E14").

 

[David117]

This one:
actually column E from whatever row matches (i.e. if it matches row 14, then you want to return "E14").

 

[Joe4]

OK, then you will need to use an INDEX/MATCH on multiple conditions as described here: INDEX and MATCH with multiple criteria

The formula would look something like:

=INDEX(TemplatesRecord!E:E,MATCH(1,(TemplatesRecord!A:A=TemplatesOverview!A2)*(TemplatesRecord!B:B=TemplatesOverview!B2)*(TemplatesRecord!C:C=TemplatesOverview!C2)*(TemplatesRecord!D:D=TemplatesOverview!D2),0))

You can make the formula more efficient if you replace all the absolute column references (i.e. "A:A") with a specific range (i.e. "A2:A100"). Just make sure that all the ranges in yuor formula have the exact same number of rows.

 

[David117]

AMAZING!!!!
Thank you so much for this