Forum Discussion

Copper Contributor

Aug 20, 2018

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 colum...

Isuzu List for category (sample).xlsx1.3 MB

Isuzu List for category.xlsx1.9 MB

excel

pranav trikha

Brass Contributor

Aug 20, 2018

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,

Quinn Fung-A-Wing
Copper Contributor
Aug 21, 2018
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
- pranav trikha
  Brass Contributor
  Aug 21, 2018
  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