Vlook query

[rvd48]

hi, i want to look up data so i can do some conditional formatting.

=VLOOKUP(E1,A1:C10,2,FALSE)

E1 says: 'oranges'

so the vlookup looks at the cell range of A1:C10 for oranges, it finds it and gets the value next to oranges and reports it back.

but say E1 was 'oranges + 2 pips'

could i change the statement so it would be a bit vague bit still just look for oranges?

regards,
rvd

 

[namliam]

you could do something with looking for the first word (until the first space) but that is going to be far from perfect...

 

[rvd48]

ok, this is proving to be a problem for me.

the vlookup template: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

now, in the 'lookup_value' cell the user can enter a text like "RS5000 + racket" but my table array will contain a specific product list such as:

col 1 ...... col 2
RS5000 .... £50.000
RK8850.... £66.00

so i need my vlookup to be less specific and just look for the key term 'RS5000'.

can i amend my formula to do this?

 

[namliam]

you could do something with looking for the first word (until the first space) but that is going to be far from perfect...

Like I said, you can do something with the space... but there are troubles on the horizon... (what is there is no space?)

If the user enters "RS5000 + racket" into Cell A1, using this as Lookup_Value
TRIM(LEFT(A1,FIND(" ",A1,1)))
Will remove the " + racket" part leaving the RS5000...

I hope this is what you are looking for...

 

[rvd48]

mailman, that just leaves a #value error

 

[namliam]

RVD please see attached 2002 excel sheet.