Vlookup help!

**Catdumana** · 12-08-2010, 06:24 PM

I need help, I am not even sure there is a way t do what I need. The following is a formula I already have working, however, I need to add one more thing to it. The cell it references has more information then I need in it and I just want to return the first word in th cell. In this case it is the vendor name. The isna is to keep the #NA from showing. Can anyone help? Excel 2003

IF(ISNA(VLOOKUP(B$2:B$22,'PURCHASE ORDERS'!A$2:L$207,12,FALSE)) = TRUE, " ", VLOOKUP(B$2:B$22,'PURCHASE ORDERS'!A$2:L$207,12,FALSE))

Cat

The following is how my formula looks now and it works, thank you for your help!
=IF(ISNA(VLOOKUP(B$2:B$52,'PURCHASE ORDERS'!A$2:L$207,12,FALSE)),"",LEFT(VLOOKUP(B$2:B$52,'PURCHASE ORDERS'!A$2:L$207,12,FALSE),FIND(" ",VLOOKUP(B$2:B$52,'PURCHASE ORDERS'!A$2:L$207,12,FALSE))-1))

**TMS** · 12-08-2010, 06:38 PM

A simplified example:

=IF(ISNA(VLOOKUP(A2,H2:J15,2,FALSE)),"",LEFT(VLOOKUP(A2,H2:J15,2,FALSE),FIND(" ",VLOOKUP(A2,H2:J15,2,FALSE))-1))

Note: you don't need the "=TRUE"

Regards

**Catdumana** · 12-09-2010, 10:35 AM

Thank You for your help, with a little tweaking (=IF(ISNA(VLOOKUP(B$2:B$52,'PURCHASE ORDERS'!A$2:L$207,12,FALSE)),"",LEFT(VLOOKUP(B$2:B$52,'PURCHASE ORDERS'!A$2:L$207,12,FALSE),FIND(" ",VLOOKUP(B$2:B$52,'PURCHASE ORDERS'!A$2:L$207,12,FALSE))-1))) I got it to work in my worksheet.

Cat

**Catdumana** · 12-09-2010, 01:07 PM

You are welcome and I did learn quite a bit that way. The reason I had true in there is because of the example I found when trying to figure out how to keep the #NA from showing. As for the range, this is how I was taught to do it way back when. I have a work book that has many sheets in it. One sheet is my part master which is used to populate my on hand and on order columns, then I have a purchase order sheet that populates the PO number and now the vendor that PO is issued to. The rest are my bills of material. So if I got you right I only need the cell it needs to look for even if it is looking on another worksheet for it?

Cat

**Catdumana** · 12-09-2010, 01:57 PM

So I took out the range and just put in the cell and wa la it works, once again thanx I learned something new!

Cat

**TMS** · 12-09-2010, 04:12 PM

Yep, ...

VLOOKUP(B$2,'PURCHASE ORDERS'!A$2:L$207,12,FALSE)

should do it. Give it a try.

Regards

**Catdumana** · 12-09-2010, 04:17 PM

I did and it worked, still fixing them! I have a lot of worksheets in this one. I tried to upload it so you could see it but even zipped it would not go.

Thanx again Cat