Can I use a dynamic table array in a vlookup?

2
votes

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

2
excelvlookupexcel-match
Can you please describe what you're trying to do in excruciating detail? The phrase "if the value in Column A on sheet 1 appears under a specific number on sheet 2" isn't really explaining it to me. – Enigmativity
@Enigmativity When you look at the Sample file or the Page with formulas image, you will see that I got check marks under column header 1 on BPL, EQNR, OXY, and KMI. Thats because on the referencing page, those companies are under column header 1. I would like for the companies who are under column header 2 on referencing page to also get check marks like those mentioned above. Except my Vlookup breaks down after the first column because of the table_array problem. Please check the sample file. – indecentM

2 Answers

2
votes

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

1
votes

On sheet 1, insert a column between 1 and 2, 2 and 3, 3 and 4 etc.

Then, enter this formula into cell B4 and drag across:

=IF(IFERROR(INDEX('Peer Peer Details'!$A$3:$G$6,MATCH($A4,'Peer Peer Details'!A$3:A$6,0),MATCH('Peer to Peer Statistics'!B$2,'Peer Peer Details'!$A$1:$G$1,0)),0)<>0,"P","")

Then, you can delete the columns or hide the columns, etc.

enter image description here