LOOKUP type function with 2 different values

[gareths]

I have 2 sheets in my workbook on sheet1 there is a value for x in Q2 an a value for y in R2. In sheet 2 there are 3 columns - A contains x values, B contains Y values and C contains postcodes. What I would like to do is check the value of Q2 against those in column A on sheet2 and as soon as a value in A is higher than Q2 then R2 is checked against column B from that point until a value in B is higher than R2 and then the corresponding postcode in column C is the output.

For example: Q2 = 500877, R2 = 151590

on sheet2

_____A_______B________C
__141304___934035___HS20DB
__508783___100001___RH149RU
__508783___160389___TW178HL
__617755___168208___CT65JY

so here Q2 is less than 508783 in A and R2 is less than 160389 in B so the output is TW178HL.

 

[T. Valko]

I have 2 sheets in my workbook on sheet1 there is a value for x in Q2 an a value for y in R2. In sheet 2 there are 3 columns - A contains x values, B contains Y values and C contains postcodes. What I would like to do is check the value of Q2 against those in column A on sheet2 and as soon as a value in A is higher than Q2 then R2 is checked against column B from that point until a value in B is higher than R2 and then the corresponding postcode in column C is the output.

For example: Q2 = 500877, R2 = 151590

on sheet2

_____A_______B________C
__141304___934035___HS20DB
__508783___100001___RH149RU
__508783___160389___TW178HL
__617755___168208___CT65JY

so here Q2 is less than 508783 in A and R2 is less than 160389 in B so the output is TW178HL.

What if the X and Y values ARE EQUAL to the Q2 and R2 values? You specifically asked for GREATER so that's how I wrote the formula.

Book1
	A	B	C
2	141304	934035	HS20DB
3	508783	100001	RH149RU
4	508783	160389	TW178HL
5	617755	168208	CT65JY
Sheet2

Book1
	Q	R	S
2	500877	151590	TW178HL
Sheet1

This array formula** entered in S2:

=INDEX(Sheet2!C2:C5,MATCH(1,IF(Sheet2!A2:A5>Q2,IF(Sheet2!B2:B5>R2,1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[gareths]

Yeah I mean greater than or equal to - I wasn't sure what statements you could use. I'll try that later thanks!

 

[T. Valko]

Yeah I mean greater than or equal to - I wasn't sure what statements you could use. I'll try that later thanks!

OK, just change the > operators to >=.

 

[gareths]

Yeah that worked thanks, but the results I got back were not the ones required due to the lookup data not being correct therefore I am now using a web query to find the postcodes from an external site.

Private Sub commandbutton1_click()
    www = "URL;http://api1.nearby.org.uk/api/convert.php?key=431b753ede6408&p=" _
    & Range("U2") & "&want=postcode-uk&output=text"
    With ActiveSheet.QueryTables.Add(Connection:=www, Destination:=Range("W2"))

        .Name = "convert.php?key=431b753ede6408&p=""""&want=postcode-uk&output=text"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """postcodes"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

I'm just wondering if I'm able to change W2 so it is the active cell and U2 so it is the active cell - 2.

 

[T. Valko]

Yeah that worked thanks, but the results I got back were not the ones required due to the lookup data not being correct therefore I am now using a web query to find the postcodes from an external site.

Private Sub commandbutton1_click()
    www = "URL;http://api1.nearby.org.uk/api/convert.php?key=431b753ede6408&p=" _
    & Range("U2") & "&want=postcode-uk&output=text"
    With ActiveSheet.QueryTables.Add(Connection:=www, Destination:=Range("W2"))
 
        .Name = "convert.php?key=431b753ede6408&p=""""&want=postcode-uk&output=text"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """postcodes"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

I'm just wondering if I'm able to change W2 so it is the active cell and U2 so it is the active cell - 2.

I'm not much of a programmer so I can't help you with that.

If, after a day or 2, no one else responds try starting a new thread.