Return the TXT in colG if colA is.....

[ermccarthy]

I have a large table of data on my worksheet. I need a formula that will pull the Value of column G (text), of the row that in column A has "o1". The good news is, there will only ever be 1 "o1" value on the worksheet in column A. The bad news is, I can not sort the data on this worksheet (so vlookup will not work)

I have a number of these codes in column A to prioritize items on the worksheet. We use o1 to o5, d1 to d5, f1 to f5, and c1 to c5. So I will use this formula often!!

Thanks!!

 

[gsistek]

If there is only one "o1" then you can use =VLOOKUP("o1",$A:$G,7,0)

 

[T. Valko]

I have a large table of data on my worksheet. I need a formula that will pull the Value of column G (text), of the row that in column A has "o1". The good news is, there will only ever be 1 "o1" value on the worksheet in column A. The bad news is, I can not sort the data on this worksheet (so vlookup will not work)

I have a number of these codes in column A to prioritize items on the worksheet. We use o1 to o5, d1 to d5, f1 to f5, and c1 to c5. So I will use this formula often!!

Thanks!!

When looking for "exact" matches the data DOES NOT need to be sorted for VLOOKUP.

So, try using VLOOKUP!

=VLOOKUP("o1",A:G,7,0)

Or...

=INDEX(G:G,MATCH("o1",A:A,0))

 

[ermccarthy]

..

 

[Aladin Akyurek]

I have a large table of data on my worksheet. I need a formula that will pull the Value of column G (text), of the row that in column A has "o1". The good news is, there will only ever be 1 "o1" value on the worksheet in column A. The bad news is, I can not sort the data on this worksheet (so vlookup will not work)

I have a number of these codes in column A to prioritize items on the worksheet. We use o1 to o5, d1 to d5, f1 to f5, and c1 to c5. So I will use this formula often!!

Thanks!!

If there is only one occurrence of a criterion of interest, try...

=LOOKUP(9.99999999999999E+307,1/(A2:A100="o1"),G2:G100)

If there are multiple occurrences...

I2: o1

I3:

=COUNTIF(A2:A100,I2)

I5, control+shift+enter, not just enter, and copy down:

=IF(ROWS($I$5:I5)<=$I$3,INDEX($G$2:$G$100,
     SMALL(IF($A$2:$A$100=$I$2,ROW($G$2:$G$100)-ROW($G$2)+1),
       ROWS($I$5:I5))),"")