Combine vlookup with between...

**Philistine** · 03-18-2011, 10:37 AM

I'm currently struggling with a formula where I try to combine a vlookup with a value that can be equal or inbetween a list of two values in two columns. This is all based on text strings.

The lookup should be based on a 3 column sheet:

TextA | TextC | Textnew1
TextD | TextG | Textnew2
TextH | TextM | Textnew3

When I lookup TextB of another spreadsheet I would like to get 'Textnew1' as a result.

Anyone has a clue?

**NBVC** · 03-18-2011, 10:43 AM

If the first column in your table is listed in ascending sorted order, then you can use LOOKUP()

e.g

=LOOKUP(A2,H1:J3)

this will look for the value that is in A2 and try to find it in H1:H3, it will match the last entry that is smaller that or equal to that lookup value and return the item in column J in corresponding row.

**Philistine** · 03-18-2011, 10:46 AM

Unfortunately there is no direct match possible.

TextA - TextC is a range.

TextB is somewhere inbetween and needs to be detected. If it would be TextA or TextC no prob. I wouldn't need any help.... ;-)

**NBVC** · 03-18-2011, 10:55 AM

Test my formula...

with your sample table in H1:J3 and your lookup value of TextB in A1. You should get expected result.

LOOKUP is an approximate match, not an exact match.

**Philistine** · 03-18-2011, 11:06 AM

I get a wonderful #N/A although the range is mapped.

I have tried
=LOOKUP(B8,'Sheet1'!A2:B149,'Sheet1'!C2:C149)
as well as
=LOOKUP(B8,'Sheet1'!A2:C149)

In both cases the outcome is #N/A

I have re-checked the format should be the same, it's all text.

**NBVC** · 03-18-2011, 11:11 AM

This is what I get with your sample data...

Can you post a more representable example of your actual data... I am sure it is not as basic as TextA, TextB, TextC

**Philistine** · 03-18-2011, 11:16 AM

Cool! Found my mistake!

My formula
=LOOKUP(B8,'Sheet1'!A2:B149,'Sheet1'!C2:C149)

should have been
=LOOKUP(B8,'Sheet1'!A2:C149,'Sheet1'!C2:C149)

Looking into it too long one gets blind....

Et voila - the result.

Thanks a million.

**NBVC** · 03-18-2011, 11:18 AM

In that second case, then you don't actually need the 3rd argument..

just: =LOOKUP(B8,'Sheet1'!A2:C149) should suffice.

**Philistine** · 03-18-2011, 11:20 AM

True, that also works.

**NBVC** · 03-18-2011, 11:23 AM

Please mark your thread as Solved.

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