MS Excel 2007 - Search between list of two ranges on rows

[MSealy]

Hi,

I'm trying to search between two sets of data across multiple lines. (That's not very decriptive, so I'll give an example)

A B C
1 1 1 259
2 2 260 322
3 3 323 460
4 4 461 513

In D1 I have a search box. The idea being, if I put say 280 in D1, I'll get the result of A2 as the 280 is between 260 and 322.

Is this a VLOOKUP job, or is INDEX the way to go about it? I've tried various SUM & tinkered with BETWEEN but can find a way to return the data from column A. It looks fairly simply in theory - am I missing something obvious?

Regards,

Mark.

 

[SkipVought]

If your data is sorted in ascending or descending order, you can use the MATCH() function with Match Types of 1 or -1 respectively.

To return the actual value, used the INDEX() function....
[tt]
=INDEX(B:B,Match(D1,B:B,1),1)
[tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MSealy]

Thanks, after changing the first bit, it worked perfectly!

=INDEX(A:B,Match(D1,B:B,1),1)

Now if I wanted to have another column, using the same rows, i.e:

E F G
1 11 514 590
2 12 591 634
3 13 635 720
4 14 721 800

Then I should be able to do this:

=INDEX(A:B,Match(D1,B:B,1),1),INDEX(E:F,Match(D1,F:F,1),1)

But it doesn't like that, nor:

=INDEX(A:B,Match(D1,B:B,1),INDEX(E:F,Match(D1,F:F,1),1))

Or:

=INDEX(A:B,Match(D1,B:B,1),1) OR INDEX(E:F,Match(D1,F:F,1),1)

I can't see why the OR statement won't work. Seems very logical to me.

 

[SkipVought]

I can't see why the OR statement won't work. Seems very logical to me.

Have you checked Excel HELP on the OR function? It will make things perfectly clear.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MSealy]

There's always more than one way of solving a problem so I did this method.

On Sheet2 I put both formulas in separate cells. I modified each as follows:

In box A1:

=IF(ISNA(MATCH(Sheet1!D1,Sheet1!B:B,1)*1),0,(INDEX(Sheet1!A:B,MATCH(Sheet1!D1,Sheet1!B:B,1),1)))

This gave me the result of the first column (1-50) on the search in D1.

In box A2:

=IF(ISNA(MATCH(Sheet1!D1,Sheet1!F:F,1)*1),0,(INDEX(Sheet1!E:F,MATCH(Sheet1!D1,Sheet1!F:F,1),1)))

This gave me the result of the first column (51-100) on the search in D1.

To merge these results into one answer (still in Sheet2) I put the following in A3:

=IF(A2>50,0,A1)

and in A4:

=A2+A3

Back in Sheet1, next to the search box (D2) I put:

=Sheet2!A4

and voila!

Not the best way round, but with my limited understanding it was the only way I could come up with. Also, I perform a search outside the ranges on the sheet, I get a "#VALUE" in D2, and in Sheet2 I get "OPENED:" - but what the hell. I can live with it. It works, and that's all that matters.