Automatically populate a cell with predefined text, based on text from another sheet

Question

Good day,

I would like to automatically fill a cell with predefined text (Customer name), based on a value (product number) that I type in.
Whenever I type the product number into the "Order" column, I would like the customer column to automatically show me

to who it corresponds.

I have a list of my Customers and the product numbers that are always unique but totally random,

so I cannot base my selection or search on ranges. (say 100 to 200 is Customer A, 300 to 350 is Customer B - this I cannot do)
This list is kept on a separate sheet in the same excel file.

Every day I keep separate work-log (spreadsheet) of the products processed on that day. Product numbers I type in manually

and it would be very helpful to instantly see the Customer's name right next to it.

I did try "IF" and "VLOOKUP" functions, but I didn't work. I probably have made an error somewhere, but I can't find it.

Thank You in advance for help.

Answer 1

Hi,

In cell C2, enter this formula

=TOROW(IF(List!$B$2:$F$6=Log!B2,List!$B$1:$F$1,NA()),3,TRUE)

Hope this helps.

Answer 2

Dear respected Wojciech Cieslicki,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

I’m sorry you’re having a problem and for the situation you encountered, we deeply understand the inconvenience caused and apologize for it.

As per your description, we may need more time to test it from our side, I request you, if possible, could you please allow me some time and I will update you here ASAP.

Appreciate your patience and understanding and thank you for your time and cooperation.

Sincerely,

Waqas Muhammad

Answer 3

=FILTER(List!$B$1:$F$1,BYCOL(List!$B$2:$F$6=Log!B2,LAMBDA(a,OR(a))))

Answer 4

Thank You, Ashish - it worked!

Much appreciated.

Answer 5

Thank You very much for Your help.