LOOKUP question

[MondeoST24]

I've got some data on sheet 3

CAPID Make Model Derivative

1001 AUDI A3 1.6 HATCH
1002 BMW 318 318 SE
1003 CITOEN C3 HATCH
1004 FIAT PUNTO 1.2 ACTIVE

On sheet one columsn A,B,C are input by the user as make, model, derivative. When the 3 cells have been filled I need to show the capid in column D.

Do I use VLOOKUP?

thanks

 

[xld]

=INDEX(Sheet3!A:A,MATCH(1,(Sheet3!B2:B200=A2)*(Sheet3!C2:C200=B2)*(Sheet3!D2:D200=C2),0))

this is an array formula, so commit with Ctrl-Shift-Enter

Not that the ranges in the matc h cannot be whole columns

 

[lynxbci]

you could use a column with a unique id in it

it would be in column a and be make&model&derivative all concatenated

you then have

Unique Capid
AUDI A3 1.6 HATCH 1001
BMW 318 318 SE 1002
CITOEN C3 HATCH 1003
FIAT PUNTO 1.2 ACTIVE 1004


then have your input table

A1 =make
A2 =model
A3 = Derivative

=lookup(A1&A2&A3,a:b,2,false)

Does that make sense

 

[MondeoST24]

=INDEX(Sheet3!A:A,MATCH(1,(Sheet3!B2:B200=A2)*(Sheet3!C2:C200=B2)*(Sheet3!D2:D200=C2),0))

this is an array formula, so commit with Ctrl-Shift-Enter

Not that the ranges in the matc h cannot be whole columns

Hi, thanks for this, it returns #VALUE! - do you know what the problem might be?

Thanks

 

[lynxbci]

its a CSE formula, you need to go into the formula edit line, and press ctrl@shift@enter to make it work, instead of just enter

I tried it and it does work ok