Return value

[Pat]

Example:

sheet1 column A contains the names of car manufactures
sheet2 column C contains the name of a car manufacture
sheet1 column F contains the make of a car manufacture

=IF(sheet1!$A$7:$A$100,sheet2!$C76,sheet1!$F$7:$F$100)

the formula which is not correct should return the make(s) of a car
manufacture if applicable.

Anyone got the know-how on this?
Thanks

 

[Frank Kabel]

Hi
try
=VLOOKUP(sheet2!$C76,sheet1!$A$7:$F$100,6,0)

for getting the first match of your car manufacturer

 

[JulieD]

Hi Pat

you need to use VLOOKUP
=VLOOKUP(sheet2!$C76,Sheet1!$A$7:$F$100,6,0)

this will look up the value in C76 of sheet 2 and return the make of the car
in column F associated with the manufacturer in column A of sheet 1.

Note, this will only return ONE make of car - the first one it finds in
column F.

Cheers
JulieD

 

[Pat]

Hello Frank

That worked fine only more than one value maybe returned. There will
possibly be more than one value returned as each manufacture will have
various models.
Also if A happened to be column R which matched C76 as I am showing here
what change would be necessary.

=VLOOKUP(sheet2!$C76,sheet1!$F$7:$R$100,6,0)

Pat

 

[Frank Kabel]

Hi
for returning multiple results you may first define HOW you want them
returned 8in one cell, in a cell range, etc.)

For your second question try:
=INDEX(sheet1!$F$7:$F$100,MATCH(sheet2!$C76,sheet1!$R$7:$R$100,0))

 

[Pat]

Using INDEX worked fine.

With regards multiple results there could be up to 3 results returned in one
cell.