Vlookup

[bmonaweck02]

I am looking for a way to use a VLOOKUP. What I am trying to do is have a VLOOKUP value for a piece of equipment. But what I would like to do is have the value it would be trying to lookup be like "PU202" but I don't want to have to create a table that has all of my equipment and their truck numbers. I just want to have a table that says the rate for this type of equipment. And not all of the equipment #'s.

If I made a table and the PU column said PU000 would the VLOOKUP function be able to lookup PU202 on a table that only says PU000 or PUXXX or something like that?


Thanks in advance.

Brian

[Philb1]

Hello Brian
Is this the sort of thing you're looking for?

[bmonaweck02]

No, Thats what I don't want to do though. I want to be able to do something like I attached. On one sheet I want to have the VLOOKUP lookup the truck rate. But I want it to lookup PU 124 where there will be no PU 124 in the table.

Thanks,
Brian

[NBVC]

Try:

=VLOOKUP("*"&LEFT(B5,FIND(" ",B5)-1)&"*",Rates!$C$5:$D$6,2,FALSE)

copied down and to next column(s)

[bmonaweck02]

Try:

=VLOOKUP("*"&LEFT(B5,FIND(" ",B5)-1)&"*",Rates!$C$5:$D$6,2,FALSE)

copied down and to next column(s)

Can you explain what all that means... HAHA.. just so I know what I am doing.


Thanks,
Brian

[NBVC]

This part: LEFT(B5,FIND(" ",B5)-1) is extract the left side of B5 to where it Finds a space in B5, FIND(" ",B5)-1 finds the space and goes back one so that the space is not part of what is extracted.

The * are wildcards and when on both sides of a value means "contains"...

So Vlookup in your table where the lookup column contains the left side (upto the space) of B5 contents.

[bmonaweck02]

One more question, how can I add to that formula to make it equal 0 if there is nothing for it to look up?

[NBVC]

You mean in B5 is blank?

=IF(B5="",0,VLOOKUP("*"&LEFT(B5,FIND(" ",B5)-1)&"*",Rates!$C$5:$D$6,2,FALSE))

[bmonaweck02]

You mean in B5 is blank?

=IF(B5="",0,VLOOKUP("*"&LEFT(B5,FIND(" ",B5)-1)&"*",Rates!$C$5:$D$6,2,FALSE))

Exactly, Thank You very much!!!

[bmonaweck02]

Is there any way to leave the spot blank instead of a 0, if B5 is blank?

[bmonaweck02]

nvrmind got that too. thanks