Excel Lookup question [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Im very basic excel user, Im trying to Search a Database of 6500+ entrys, Its a basic search that i cant seem to work correctly.

Basically Each row is a Separate entry per item, some items have 3 rows some have 5+ rows

A= item number b= item type d-f= useless info that cannot be deleted H=part number

NOw ultimately I want to search for Item type 101, inside Item number 202 and come up with a correct part number

So first i need to Search for Item number, with a second search for item type to give me Part number, it sounds so easy in my head, but i cant seem to find the correct formula.

example of my work is, I have a type of paint, in that paint is different colors, each color has a separate bar-code label with mixing instructions, I need to create an easier search method then Ctrl-f.

Thanks in advance for anyhelp



1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
supose your sample data base is like this

item type h3 h4 he h6 h7 "part
no."
1 a 12
1 s 23
1 d 34
2 a 45
2 d 56

in an empty cell type this formula

=INDEX(H1:H6,MATCH(1,(A1:A6=1)*(B1:B6="S"),1))

INVOKE THIS FOMULA BY CONTROL+SHIFT+ENTER
you will get 23

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!