look up data form table

[slbguy]

Have one data table with 200+ codes<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
<o> </o>
The data table looks like this<o></o>
code # bit mfg bit type<o></o>
2001 HTC E5001FG<o></o>
<o></o>
Main worksheet<o></o>
<o> </o>
In the main table (see image) I want to look up the code# biased on the information in columns e & f and place the code# in column d<o></o>



http://img99.imageshack.us/img99/5581/excel1.png

 

[Aladin Akyurek]

Looks like...

Control+shift+enter, not just enter:

=INDEX(CodeRange,MATCH(1,BitMfgRange=BitMfg,IF(BitTypeRange=BitType,1)),0))

Adjust to suit.

 

[slbguy]

i cant get the formula to work.

i replaced "CodeRamge" with ...bitcode'!A2:A200.. and the same for the other ranges

it only returns an error.

also i dont understand the "control+shift+enter" referance

Thank You

 

[jeffreybrown]

I don't know if Aladin is still around so I will try and help.

With your range bitcode'!A2:A200 it seems to be missing a ' or maybe you just didn't copy that part.

As for the Ctrl+Shift+Enter this a CSE formula which will require before you leave the cell with the formula you press Ctrl+Shift+Enter all together and the formula will be encased in {}. Do not try to enter these manually.

 

[jeffreybrown]

ADVERTISEMENT

http://www.mrexcel.com/articles/CSE-array-formulas-excel.php

 

[Aladin Akyurek]

i cant get the formula to work.

i replaced "CodeRamge" with ...bitcode'!A2:A200.. and the same for the other ranges

it only returns an error.

also i dont understand the "control+shift+enter" referance

Thank You

What kind of error? Would you post the full formula as you adjusted it?

Array formulas needs to be signalled distinctly. That is why you confirm such a formula, not just with the ENTER key, but with the combination of keys, CONTROL + SHIFT + ENTER, which you hit at the same time.

 

[slbguy]

ADVERTISEMENT

{=INDEX('bit code'!$A$2:$A$201,MATCH(1,'bit code'!$B$2:$B$200=E2,IF('bit code'!$C$2:$C$201=F2,1)),0)}

Here is the formula that i am using. it returns #N/A

 

[Aladin Akyurek]

{=INDEX('bit code'!$A$2:$A$201,MATCH(1,'bit code'!$B$2:$B$200=E2,IF('bit code'!$C$2:$C$201=F2,1)),0)}

Here is the formula that i am using. it returns #N/A

The ranges must be equally sized...

=INDEX('bit code'!$A$2:$A$201,MATCH(1,'bit code'!$B$2:$B$201=E2,IF('bit code'!$C$2:$C$201=F2,1)),0))

 

[slbguy]

=INDEX('bit code'!$A$2:$A$201,MATCH(1,'bit code'!$B$2:$B$201=E3,IF('bit code'!$C$2:$C$201=F3,1)),0)

still no luck?

 

[Aladin Akyurek]

=INDEX('bit code'!$A$2:$A$201,MATCH(1,'bit code'!$B$2:$B$201=E3,IF('bit code'!$C$2:$C$201=F3,1)),0)

still no luck?

I see we lost an IF while underway...

=INDEX('bit code'!$A$2:$A$201,MATCH(1,IF('bit code'!$B$2:$B$201=E3,IF('bit code'!$C$2:$C$201=F3,1)),0))