excelvlookupexcel-match

Can I use a dynamic table array in a vlookup?

I need a checkmark if the value in Column A on sheet 1 appears under a specific number on sheet 2. I tried a vLookup + Match and it only works for the first group because Vlookup array locks to that column. I tried index match but it appears multiple matches in the row part is a problem. Helllppp. I attached a sample and photos.Here are the formulas I tried:

=IFERROR(IF(VLOOKUP($A4,'Peer Peer Details'!$A$3:$G$6,MATCH(B$2,'Peer Peer Details'!$A$1:$G$1,0),FALSE)=$A4,"P",""),"")
=IFERROR(IF(INDEX($A$4:$A$16,MATCH($A4,'Peer Peer Details'!$A$3:$H$6,0),MATCH(C$2,'Peer Peer Details'!$A$1:$H$1,0))=$A4,"P",""),"")

Page with the formulas

Referencing Page

Sample

Solution

  • This worked for me:

    =IF(ISNA(MATCH(RC1,OFFSET('Peer Peer Details'!R3C1:R6C1,0,MATCH(R2C,'Peer Peer Details'!R1C1:R1C13,0)-1),0)),"","P")

    Now, most people don't work in R1C1 mode (which I don't understand why) so cell B4 is this:

    =IF(ISNA(MATCH($A4,OFFSET('Peer Peer Details'!$A$3:$A$6,0,MATCH(B$2,'Peer Peer Details'!$A$1:$M$1,0)-1),0)),"","P")

    You can then just copy the formula to the rest of the cells.

    This is what I got with your sample spreadsheet:

    sample