Excel Lookup formula problem

[RodG]

I have a list of cost codes for construction projects. The list has
column for cost codes, The subcontractor name, the subcontractor vendo
number, and the amount we have contracted for the base house and fo
the options we offer if that option was selected for this house. Fro
another sheet, I am trying to create a formula that will look up th
specific base house cost code and apply the vendor and contract amoun
on that order form. I did that successfully. The lower part of th
order form is for options. There are usually numerous options selecte
for the same vendor and I want to write a formula that will list all o
the option codes and amounts that were selected and put on that list.
I have tried the following:
=VLOOKUP(SUBCONTRACTOR, DATA RANGE,COLUMN FOR OPTION NUMBER)
=VLOOKUP(SUBCONTRACTOR,DATA RANGE, COLUMN FOR OPTION NUMBER<>RESULT O
LAST FORMULA)

My theory is that I could simply keep repeating the second formul
until there are more than I would need, but It keeps giving me th
vendor name, the same number or the subcontractor number.

Any help would be tremendously appreciated. Thanks in Advance!

Ro

 

[Frank Kabel]

Hi
not quite sure but try
=VLOOKUP(SUBCONTRACTOR, DATA RANGE,COLUMN FOR OPTION NUMBER,FALSE)

 

[RodG]

That was a great help and works well. I still have one problem
haven't been able to work out. There are multiple items for eac
subcontractor in the option section of the list. The subcontracto
name and subcontractor number are always the same. The option numbe
is always different and the amounts are usually different. There is n
way to know which options will show up on the list. I need a formul
to lookup the next option required of that subcontractor and the next
and the next and list them on a seperate work order form. Is i
possible to create a formula that will return those unique options.
The formula I have works great for the first one, but keeps returnin
the same value.

Thank you again.

Rod

 

[Mark]

use the DGET function if you need to do a lookup based on multiple criteria.

This is probably one of the cleanest ways to lookup a value based on
multiple criteria. It has problems if you need to find the closest match
without going over. In which case one of the LOOKUP functions must be used.

See Lookup.zip at http://www.xl-logic.com/pages/formulas.html

Mark