CODEWITHSUNDEEP
excelreferencematch
J. Doe
J. Doe

Reputation: 483

Excel: Find Value Underneath text with HLOOKUP in a Range defined as a Formula

enter image description here

In the defined range (red rectangle) I'm trying to find the Value (text) underlined green and then have the value underneath it read out. This is part of getting all the values of the unstructured table into another structured table.

Main Goal is it to get the text in a predefined range, which is defined by searching for the first and the second component which in this case is "Pack" and "PET" the next would be "PET" and "Orange" and so on. paired with searching for the different rounds like 0 and 1 So I'll get exactly the red square but for every component in each round

Best attempt so far:

=INDEX(purchasing!B1:B17,MATCH("Lower bound (weeks)",purchasing!B1:B17,0)+1,1)

but how do i get the range from the other table which is calculated by a formula into where B1:B17 stands? Indirect wouldn't do it, but why? used it for a lot of other very similar tasks where array-table was asked. here it is array, can a convert it somehow?

INDIRECT("'PURCHASING'!"&ranges!B2)

Using the Formula:

=HLOOKUP("Lower bound (weeks)",A1:E17,2,FALSE)

gives me #N/A While:

=LOOKUP("Lower bound (weeks)",A1:E17)

gives me the result: Delivery window

third try was this formula:

=INDEX(A1:E17,MATCH("Lower bound (weeks)",A1:E17,0)+1,1)

again only an #N/A, the only formula giving me the right result would be:

=INDEX(B1:B17,MATCH("Lower bound (weeks)",B1:B17,0)+1,1)

but then this is not the range i figured out by formula in another table. So i don't know this range in advance why i tried to do it with offset. Also gave me a wrong result because Offsets sets off the position of the cell and not of the value in it.

The Formula for the Offset in the other table is:

=VLOOKUP("contract index",INDIRECT("'PURCHASING'!"&ranges!B2),2,FALSE)

and then in the table ranges!B2 it is:

=G4&":"&H5

which gets the single values of the serached terms Pack, PET, Orange and so on

=ADDRESS(MATCH($O4,INDIRECT("'PURCHASING'!"&G$3),0),MATCH(G$2,purchasing!$1:$1,0)-1,4)
=ADDRESS(MATCH($O5,INDIRECT("'PURCHASING'!"&H$3),0),MATCH(H$2,purchasing!$1:$1,0)-1,4)

Any way around this issue?

The Ranges table which contains all the ranges where it should search for the text and if it is there give me the number underneath. All the ranges are searched automatically by a formula

enter image description here

Formula in B2

=G4&":"&H5

Formula in G2

=ADDRESS(MATCH($O4,INDIRECT("'PURCHASING'!"&G$3),0),MATCH(G$2,purchasing!$1:$1,0)-1,4)

Formula in G12

=SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(G4&"1"))+1,4),"1","")&SUBSTITUTE(G4,SUBSTITUTE(ADDRESS(1,MATCH(G$2,purchasing!$1:$1,0)-1,4),"1",""),"")

Upvotes: 2

Views: 490

Answers (2)

J. Doe
J. Doe

Reputation: 483

I used following Formula in the final sheet in cell R2 to resolve my issue:

=IF(ISNA(INDEX(INDIRECT("'PURCHASING'!"&TEXT(ranges!G12,"")),MATCH("Lower bound (weeks)",INDIRECT("purchasing!"&TEXT(ranges!G12,"")),0)+1,1)),"NULL",INDEX(INDIRECT("'PURCHASING'!"&TEXT(ranges!G12,"")),MATCH("Lower bound (weeks)",INDIRECT("purchasing!"&TEXT(ranges!G12,"")),0)+1,1))

enter image description here

this formula points to the sheet you seen on the picture underneath to cell G12 with the value of B2:B17 in it. Formula to get the Range of B2:B17 is:

=SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(G4&"1"))+1,4),"1","")&SUBSTITUTE(G4,SUBSTITUTE(ADDRESS(1,MATCH(G$2,purchasing!$1:$1,0)-1,4),"1",""),"")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(G5&"1"))+1,4),"1","")&SUBSTITUTE(G5,SUBSTITUTE(ADDRESS(1,MATCH(G$2,purchasing!$1:$1,0)-1,4),"1",""),"")

enter image description here

Upvotes: 0

QHarr
QHarr

Reputation: 84465

Use the following:

=INDEX(A1:E17,SMALL(IF(NOT(ISERROR(SEARCH("Lower bound (weeks)",A1:E17))),ROW(1:17),99^99),1)+1,SMALL(IF(NOT(ISERROR(SEARCH("Lower bound (weeks)",A1:E17))),COLUMN(A:E),99^99),1))

Enter as an array formula with Ctrl + Shift + Enter

This returns the row where found and adds one to it

SMALL(IF(NOT(ISERROR(SEARCH("Lower bound (weeks)",A1:E17))),ROW(1:17),99^99),1)+1

This returns the column where found:

SMALL(IF(NOT(ISERROR(SEARCH("Lower bound (weeks)",A1:E17))),COLUMN(A:E),99^99),1)

And then you use these for the intersect on the range indexed with 

INDEX(A1:E17,.......)

Reference:

Excel - Find a value in an array and return the contents of the corresponding column

Upvotes: 1

Related Questions