Printable View
Hi All - I've the "Item number" as lookup value in Col A. The values to be looked up (table array) is across multiple columns (6 columns). Ask: If the given item number exist in any of the 6 columns, then return the value of column 7. Tried multiple formulas including array but didn't get it done.. Can some one help? Appreciate it. PFA the excel file.
Which version of Excel do you have?
MS office Professional Plus 2021Quote:
Originally Posted by Aflatoon
try something along the lines of:Note red numbers in the formula:Code:=INDEX('Raw data'!$F$1:$F$6,MIN(IF((A7='Raw data'!$A$2:$E$6),ROW('Raw data'!$A$2:$E$6))))
(I've been lazy.)Code:=INDEX('Raw data'!$F$1:$F$6,MIN(IF((A7='Raw data'!$A$2:$E$6),ROW('Raw data'!$A$2:$E$6))))
It works. This is awesome. Thanks for the quick help. Appreciate a ton.
I believe Professional Plus 2021 has the TOCOL function, if so, you could also use:
Code:=TOCOL(IF('Raw data'!$A$2:$E$6=A5,'Raw data'!$F$2:$F$6,NA),2)
Much better!Quote:
Originally Posted by georgiboy
Mine does silly things when the part number isn't found. I was working on improving it when I saw georgiboy's solution.
Could tsr_83 confirm that TOCOL is available?
My Excel compatibility checker say's it's OK but this article implies it's not!: https://answers.microsoft.com/en-us/...1-ef1fc76b233c
TOCOL is available in my excel but need to know which cell shall i put the formula
=TOCOL(IF('Raw data'!$A$2:$E$6=A5,'Raw data'!$F$2:$F$6,NA),2)
Where would you like the result to be shown?
In output sheet, A34:A58 has the input value and want the output value in B34:58
Attachment 31854
http://www.vbaexpress.com/forum/imag...AAAElFTkSuQmCC
So it would go in B34 and you would drag it down, see attached your original attachment with formula added.
Thank you