Letter Ref in Range

[JoeExcelHelp]

I'm trying to incorporate a Match function in a hlookup that searches for the first letter "B" (Part of a work not "B" alone) in a range and returns the corresponding Hlookup function value (2)

Code: Select all

IFERROR(IF(S$13="F",(HLOOKUP(MATCH("B",$H$53:$AA$53,1),$H$53:$AA$53,2,TRUE))-IF(S$30<0,S$20,0),HLOOKUP(S$13,$H$75:$AA$95,2,FALSE)),"-")

[HansV]

The index of the first cell that contains a B in H53:A53 is MATCH("*B*",$H$53:$AA$53,0)

[JoeExcelHelp]

I tried this and it didnt work

Code: Select all

=IF(AND(S13="F",S14<0),HLOOKUP("*B*",H75:AA83,2,TRUE),0)

[HansV]

You can't combine wildcards such as * with TRUE as 4th argument in HLOOKUP (or VLOOKUP). You MUST use FALSE here.
If you use FALSE, the function will return the value from row 76 (the second row of H75:AA83) corresponding to the first match. I don't know if that is what you want.

[JoeExcelHelp]

Thanks Hans that worked.. it seems to be identifying the first "B" right to left is it possible to change the search left to right?

[JoeExcelHelp]

I may try and go a different route, If i create a formula that pulls that last "B" entry in a specific row it could work as a reference. I tried this but it didnt work can you assist?

Code: Select all

INDEX(H75:AA75,MATCH("*B*",H75:AA75))

[HansV]

HLOOKUP does look for the first occurrence from left to right, not from right to left.
If you want to use MATCH, you must specify 0 as 3rd argument, to indicate that you want an exact match. similar to the FALSE as 4th argument to HLOOKUP:

INDEX(H75:AA75,MATCH("*B*",H75:AA75,0))

[JoeExcelHelp]

Thanks Hans,

It doesnt seem to reference the cell i need.. for example within the row i have value (B1) IN H75 AND value (B2) in H76.. in this example, i need it to return B2 but it still references B1

[Rudi]

Its finding the first occurrence of a value with B in the string.

Is there not a way that you can concatenate two (or more) columns to produce a unique lookup value for the formula?

[JoeExcelHelp]

Unfort no.. Its a unique range..

[Rudi]

I'm not too sure how you will get around the issue of "duplicates" in your lookup_array???

[JoeExcelHelp]

Is it possible to have the formula reference the first letter of each cell within its range.. The format will be consistant in that a B will be the first character.. just need something that hits the frist b right to left

[HansV]

If you want the value in row 76 that corresponds to the first cell in row 75 that contains a "B":

=INDEX(H76:AA76,MATCH("*B*",H75:AA75,0))

[JoeExcelHelp]

Thanks Hans