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?
Be as you wish to seem
MS office Professional Plus 2021Originally Posted by Aflatoon
try something along the lines of:Note red numbers in the formula:=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.)=INDEX('Raw data'!$F$1:$F$6,MIN(IF((A7='Raw data'!$A$2:$E$6),ROW('Raw data'!$A$2:$E$6))))
Last edited by Aussiebear; 12-06-2024 at 05:28 PM.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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:
=TOCOL(IF('Raw data'!$A$2:$E$6=A5,'Raw data'!$F$2:$F$6,NA),2)
Much better!
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
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
In output sheet, A34:A58 has the input value and want the output value in B34:58
vlookup across multiple columns_pic.jpg
So it would go in B34 and you would drag it down, see attached your original attachment with formula added.
Thank you
Reply With Quote
