I hope you can help me with a Excel VLOOKUP problem that I have. Having read through these fora, I realise that there are many knowledgeable people here!
My problem is this;
I have a workbook with multiple sheets. On Sheet 1, I have an X & Y axis style table set up, with a set of identifier codes in the X axis, and a set of classification codes in the Y axis.
On Sheet 2, I have a simple 2 column table, which lists the identifier codes in Column 1, matched with the classification codes in Column 2.
I want the text “TRUE” to appear on Sheet 1, every time the identifier code (X axis) matches a classification code (Y axis), pulling the data from Sheet 2 (i.e. to fill in the table with TRUE if there is a match, or leave blank if not).
The problem being, many of the identifier codes are associated with multiple classification codes on Sheet 2.
I have written the formula;
• =IF((VLOOKUP(H5,’Sheet 2'!$B$5:$C$1713,2,FALSE)=$I$4),"TRUE","")
Where H5 in the Identifier Code on Sheet 1, B5:C1713 are the classification codes array on Sheet 2 and I4 is the Classification code on Sheet 1. So if the VLOOKUP pulls the classification code from Sheet 2, and it matches the Classification code on Sheet 1, I get the text "TRUE".
This formula is working to an extent, in that it returns the “TRUE” value for the first instance of the identifier code matching a classification code (the VLOOKUP Function). However, for subsequent matches, this formula is not returning the subsequent matched data. That is, I want TRUE to appear three times (in this instance), once for each VLOOKUP match, but it only appears for the first one.
So for example; the first identifier code 2xxxxx2252 is associated with the classification codes 180000015, 180000117 and 180000155 on ‘Sheet 2’. What I want here is for TRUE to appear on ‘Sheet 1’ every time any of these codes is listed, i.e. I should have TRUE three times across Row X on the sheet. It, however, only appears once for the 2xxxxx2252/180000015 association. It is putting 18000015 into each match,not 18000015 AND 180000117 AND 180000155.
I understand that VLOOKUP is a vertical only formula, so I have not used the fill handle to copy across; I have rewritten the formula for each column, referencing the correct cells.
If there is any to make a VLOOKUP formula return multiple outcomes, I would really appreciate advice to send me in the correct direction. I am very much so a beginner at Excel! I hope this makes sense.
Thank you,
Alan.
My problem is this;
I have a workbook with multiple sheets. On Sheet 1, I have an X & Y axis style table set up, with a set of identifier codes in the X axis, and a set of classification codes in the Y axis.
On Sheet 2, I have a simple 2 column table, which lists the identifier codes in Column 1, matched with the classification codes in Column 2.
I want the text “TRUE” to appear on Sheet 1, every time the identifier code (X axis) matches a classification code (Y axis), pulling the data from Sheet 2 (i.e. to fill in the table with TRUE if there is a match, or leave blank if not).
The problem being, many of the identifier codes are associated with multiple classification codes on Sheet 2.
I have written the formula;
• =IF((VLOOKUP(H5,’Sheet 2'!$B$5:$C$1713,2,FALSE)=$I$4),"TRUE","")
Where H5 in the Identifier Code on Sheet 1, B5:C1713 are the classification codes array on Sheet 2 and I4 is the Classification code on Sheet 1. So if the VLOOKUP pulls the classification code from Sheet 2, and it matches the Classification code on Sheet 1, I get the text "TRUE".
This formula is working to an extent, in that it returns the “TRUE” value for the first instance of the identifier code matching a classification code (the VLOOKUP Function). However, for subsequent matches, this formula is not returning the subsequent matched data. That is, I want TRUE to appear three times (in this instance), once for each VLOOKUP match, but it only appears for the first one.
So for example; the first identifier code 2xxxxx2252 is associated with the classification codes 180000015, 180000117 and 180000155 on ‘Sheet 2’. What I want here is for TRUE to appear on ‘Sheet 1’ every time any of these codes is listed, i.e. I should have TRUE three times across Row X on the sheet. It, however, only appears once for the 2xxxxx2252/180000015 association. It is putting 18000015 into each match,not 18000015 AND 180000117 AND 180000155.
I understand that VLOOKUP is a vertical only formula, so I have not used the fill handle to copy across; I have rewritten the formula for each column, referencing the correct cells.
If there is any to make a VLOOKUP formula return multiple outcomes, I would really appreciate advice to send me in the correct direction. I am very much so a beginner at Excel! I hope this makes sense.
Thank you,
Alan.
