Search 2 colums, display the value of a cell between the two results.

**leewcrawford** · 09-14-2008, 08:27 AM

I have three colums of data.

Column A contains valuse like this : 44a, 44u, 44b, 44a, 44u etc..
Notice that some of the values repeat.

Column B contains values that represent length in feet: 12, 10, 8, 15, 17 etc..

Column C is similar to column A : 44u, 44c, 44u, 44b, 44s etc..
Again Notice that some of the values repeat.

I want to search for a value in col. A and Col C and if they equil what im searching for display the length.

Exampl: I enter the first location in cell d1, and the second location in cell d2
I would like to then search Col. A for the first loc. and Col. C for the second loc. and return the corrasponding length from col b.

I have tried the following code and connot get it to work correcly.

=IF( (MATCH(C11, A2:A6, 0)) = (MATCH(C12, C2:C6, 0) ), INDIRECT(CONCATENATE("B", (MATCH(C11, A2:A6, 0) ) ) ), "LENGTH NOT FOUND")

I dont know where to begin when using a function that will keep searching and not just stop at the first match.

I have attached a sample of what i am trying to do.

**martindwilson** · 09-14-2008, 09:22 AM

you need this array formula

Please Login or Register  to view this content.

confirm it with ctrl+shift+enter
so {} appear around it
like this

Please Login or Register  to view this content.

**leewcrawford** · 09-14-2008, 10:11 AM

Thanks martindwilson! That works great.

Is there a way to have it preform the same function on text that might not match exactly what is being search, returning the nearest match?

example: instead of jsut having the values 44u, 44a, etc.. the data looks more like.

Col. A = rcdn9-44u-oobsw1
Col. B = 14
Col. C = 44U/C4

If the user searches for 44u-oobsw1 and 44u the return value would be 14.
I dont know if that is possable.

Again thanks. The first option you gave works if they enter the exact names, witch is awesome in its self.

**martindwilson** · 09-14-2008, 10:57 AM

i cant think of a way
but thats not to say it cant be done
depends on how mant different items there are
you could use validation lists to force user to only choose say 44u
but the first step would be to only have your table containing those values as in your posted spread sheet
it should be possible to extract them from the longer strings depending on the format
post a list of them

**leewcrawford** · 09-14-2008, 11:06 AM

Here is a sample of what the lists look like. In the end I am trying to get the cable length to auto populate based on what they type into the device fields.
It works right now based on the code you gave me earlier but only if they type it exactly.

I do like you idea of the drop down list. I am going to explore that further.

Please Login or Register  to view this content.

**martindwilson** · 09-14-2008, 11:51 AM

Fortunately the device codes follow a similar pattern so its easy to extract 44u etc see attached
i covered the possibility the shorter description may be in the first column
formulas in col e and g and dragged down
i'd do that first
then copy paste back special values
then use that table for your lookup
you could remove duplicates if you like
select the 3 columns
go to data/filter /advanced filter
check the copy to another location
check the unique records only box

click in the "copy to" window to select it then
click a cell in an empty column(make sure the next two are empty as well )click ok