Vertical Look Up in Multiple Columns

[david.frame]

I am trying to make a spreadsheet that takes an equipment number and then searches a cable database to tell me the cable_ID of every cable associated with that piece of equipment. I need to search two separate columns in the cable database: eq_cable_from and eq_cable_to. I want my output be cable_id IF eq_cable_to OR eq_cable_from = equipment number.

My variables are as follows:

tofrom = the cable database that i'm searching in
AB = column where cable_id is stored
F = column where eq_cable_from is stored
K = column where eq_cable_from is stored
A3= cell where my equipment number is stored

So far, i've gotten the correct output by just searching in one of the necessary columns using the following formula:

=INDEX(tofrom!$AB:$AB,SMALL(IF(tofrom!$F:$F=Sheet3!$A$3,ROW(tofrom!$F:$F)),ROW(tofrom!F9)))

However, I get hung up when I try to search in BOTH column F and column K. I tried adding an OR command inside of the IF command but that doesnt seem to work. Here is what that attempt looked like:

=INDEX(tofrom!$AB:$AB,SMALL(IF(OR(tofrom!$F:$F=Sheet3!$A$3,tofrom!$K:$K=Sheet3!$A$3),ROW(tofrom!$F:$F)),ROW(tofrom!F5)))

This is my first attempt at using excel and i'm very unfamiliar with the commands. The formulas above were developed from answers to similar questions on this site and i'm still trying to figure out exactly how they work. Any help would be much appreciated

[Fotis1991]

Maybe something like this, works for you?

=IFERROR(INDEX(tofrom!$AB:$AB,SMALL(IF(tofrom!$F:$F=Sheet3!$A$3,ROW(tofrom!$F:$F)),ROW(tofrom!F9))),INDEX(tofrom!$AB:$AB,SMALL(IF(tofrom!$F:$F=Sheet3!$A$3,ROW(tofrom!$F:$F)),ROW(tofrom!F9)))

[Blake 7]

i reckon your best bet may be to upload a smaple of yout data and describe your desired output there. That is unless the formula offered by Fotis arriba didnt do the job. Sorry Fotis. i didnt refresh

[Fotis1991]

No problem my friend. As you know usually i ask for a sample workbook. This time i gave a try to this suggestion.

[david.frame]

It looks like what Fotis gave me has me pretty much where i need to be. However, it is not giving me all of the cable_ID values that i need. Its starting somewhere in the middle instead of picking up the first one. I do have the "tofrom" sheet sorted a-z ascending in reference to cable_ID. I have made a small sample workbook to show my dilemma but i'm having trouble uploading it. I think my work computer blocks me from doing it

Thanks for all the help so far

[david.frame]

Attached is a sample workbook. In sheet2 I want column A to search columns F and K in "tofrom" and tell me all list all of the cable_IDs from column AB. You can see in the sample workbook how the equipment number from A3 can be in either the F column of the K column of "tofrom"

Also, thank you for the quick replies and helpfulness.

[Fotis1991]

Let;s try this.

With your code number in G1, try this ARRAY Formula.

Please Login or Register  to view this content.

Copy down.

You have to change ALL semi colons in my formula, to comma.

[david.frame]

Fotis,

That has me closer to where I need to be. However, I still need to be searching column K for my equipment number. I dont see column K in the formula anywhere. When the output is correct it will have cables labeled 512-MO-xxxxxxx and 515-MCC-0054011-Px.

In summary,
If G1 is in column f, i would get an output of 512-mo-xxxxx
If G1 is in column k, i would get an output of 515-MCC-0054011-Px

[Fotis1991]

Apologize, but i am very tired

See this.

=IFERROR(IFERROR(INDEX(tofrom!AB:AB;SMALL(IF(tofrom!F:F=Sheet2!$G$1;ROW(tofrom!AB:AB));ROW(tofrom!AB1)));(INDEX(tofrom!AB:AB;SMALL(IF(tofrom!K:K=Sheet2!$G$1;ROW(tofrom!AB:AB));ROW(tofrom!AB1)))));"")

1) Remember it's an Array formula.
2)I Suggest you do don't use Whole column reference(F:F...but certain range. eXAMPLE f1:f2000)
3) Change semi colons to comma

[david.frame]

Thank you so much for all of your help Fotis. Haha it takes a lot of patience to deal with someone with as little baseline Excel knowledge as I have.

It appears that I am now getting values from both the K and F columns. Below is the formula you gave me:

=IFERROR(IFERROR(INDEX(tofrom!AB:AB;SMALL(IF(tofrom!F:F=Sheet2!$G$1;ROW(tofrom!AB:AB));ROW(tofrom!AB1)));(INDEX(tofrom!AB:AB;SMALL(IF(tofrom!K:K=Sheet2!$G$1;ROW(tofrom!AB:AB));ROW(tofrom!AB1)))));"")

This leaves me with the first several values from the K column being omitted. If i reverse the order of calling "F:F" and "K:K" it results in the first several values of the F column being omitted. Do you (or anyone else who may be looking) have any idea why?

[Fotis1991]

If you don't find your solution, i;ll see this again tomorrow morning..

[Fotis1991]

I Apologize for the delay.

I forgot that this is a weekend and weekend belongs to my family!


Take a look to this version.

I don't have Excel >=2007 AT home, so i don;t use IFERROR function.

[david.frame]

That works perfectly. Thank you very much

[vlady]

@ David

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".
For the meantime I'll do it for you.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
**
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.

Regards,
Vladimir