# LOOKUP question

#### MondeoST24

##### New Member
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

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### xld

##### Banned
=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

##### Board Regular
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

A1 =make
A2 =model
A3 = Derivative

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

Does that make sense

#### MondeoST24

##### New Member
=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

##### Board Regular
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

Replies
2
Views
114
Replies
0
Views
125
Replies
2
Views
144
Replies
1
Views
281
Replies
3
Views
553

1,191,053
Messages
5,984,374
Members
439,883
Latest member
onions44

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back