Forum Discussion
Lookup in an array
Hi,
I'm trying to determine if the partnumber on the 2nd tab (column1) has a substitute partnumber in the array on the first tab. On the 2nd tab, The formula should return yes or no in one column (yellow) and it should return the substitute partnumbers in subsequent adjacent columns .
Can anyone assist with a formula? I'm thinking it should be some type of nested index-match function.
Thanks in advance,
Quinn
3 Replies
Greetings!
As per data set provided, first converted A2:A9 data into text and applied
Formula 1:
=IF(ISTEXT(VLOOKUP($A2,'Substitute Partnumbers Isuzu'!$A$3:$I$3172,2,FALSE)),"Yes","No")
in range V2:V9.
Other formula used
Used Helper col W2:W9
Formula 2 applied in the range above:
{=ISTEXT(INDEX('Substitute Partnumbers Isuzu'!$B$3:$B$3172,MATCH(Sheet2!$A2,'Substitute Partnumbers Isuzu'!$A$3:$A$3172,0)))}
Formula 3: range V2:V9, used output in W2:W9
=IF(W2=FALSE,"No","Yes") and applied to V2:V9
Thanks,
Hi Pranav,
Thanks for replying. Unfortunately, the dataset provided is part of a larger workbook with links to the data that you converted to Text. I cannot convert the data to text because that would destroy other links.
So, can you propose another solution with the data being numbers?
Look forward to hearing from you,
Quinn
Is it possible to use Text to column in $A:$A Substitute Partnumbers Isuzu and remove " ' ", if yes, then
can apply
=IF(ISNUMBER(MATCH(A2,'Substitute Partnumbers Isuzu'!$A$3:$A$3172,0)),"Yes","No")
in range V2:V9 , Sheet 2.
Thanks
