VLOOKUP Formula Not Working

[MariaFarag]

Hello,

I am trying to put the below formula in column B because I want it to take the customer's name from the OC no.

=IFERROR(VLOOKUP(A3,'2021 Operations List'!P5:P200, 5, FALSE), "")

the OC no. is in another sheet in the same file as shown below. You can see the formula in column E to get the customer's name from another sheet. Is this the reason that the sheet above can't read the client's name? Is there another formula which would give me the desired result above to have the customer's name displayed from the formula without changing anything in the below sheet?

thank you in advance
Maria

 

[Fluff]

Which columns on the Operations list sheet contain the OC number & the customer name.

 

[MariaFarag]

Which columns on the Operations list sheet contain the OC number & the customer name.

Hi Fluff, thank you for your reply .. the OC number is in column P and the customer name is in column E

now also i realized that i forgot to add one point to the main question, which is the following:

- for the OC number, column P is the one which has most of the numbers. but sometimes, it can be N/A and the OC number can be found in columns T or Z

so the logic is as follows:

- column P is the OC number if the order will be manufactured in our factory
- column T is the OC number if the order is going to be brought from a third party company to complement our factory order
- column Z is the OC number if the order is going to be from the stock we have in our warehouse

So sometimes we have OC numbers in one of the three columns, and sometimes in all 3 or in both. So i want the formula to look into all 3 to see where it finds the number i insert in column A of the first sheet in my main question

I hope this isn't confusing. Please let me know if you need clarification

 

[Fluff]

Ok, how about

=IFERROR(INDEX('2021 Operations List'!E2:E500,MATCH(A3,'2021 Operations List'!P2:P500,0)),IFERROR(INDEX('2021 Operations List'!E2:E500,MATCH(A3,'2021 Operations List'!T2:T500,0)),IFERROR(INDEX('2021 Operations List'!E2:E500,MATCH(A3,'2021 Operations List'!Z2:Z500,0)),"")))

 

[MariaFarag]

Ok, how about

=IFERROR(INDEX('2021 Operations List'!E2:E500,MATCH(A3,'2021 Operations List'!P2:P500,0)),IFERROR(INDEX('2021 Operations List'!E2:E500,MATCH(A3,'2021 Operations List'!T2:T500,0)),IFERROR(INDEX('2021 Operations List'!E2:E500,MATCH(A3,'2021 Operations List'!Z2:Z500,0)),"")))

hi Fluff, thanks a lot i really appreciate the help. it worked well.

I tried to work a bit with the formula you gave me to get the below needed result but it didn't work. Maybe i missed something or it has to be a different formula? what i need is the below:

I want the project name to be taken from the Operations List. in the operations list, the Project name is in column I. the same OC number applies, it might be in one of the 3 columns i mentioned before

can you please help with this one too?


thank you in advance

 

[Fluff]

Just change the E2:E500 to I2:I500

 

[MariaFarag]

Just change the E2:E500 to I2:I500

thanks Fluff it worked well

 

[Fluff]

You're welcome & thanks for the feedback.