Displaying a field from a range on the same row as a text value in another range

[jpapex]

Hello,


I am trying to display a value from Sheet1!K:K if on the same row Sheet1!B:B is equal to a text value of "P0227" or field P62 on my main tab.


Can anyone help?

 

[jpapex]

I have used SUMIF in the past to show numbers for this but I can't figure out how to show text.


Example:


=SUMIF(Sheet1!B:B, "P0227", Sheet1!K:K)


This only works with numberical values. Is there some other sort of equation that can do this with text if there is only 1 instance of the "P0227" in column B on Sheet1?


Thank you in advance...

 

[jpapex]

I have tried:


=VLOOKUP(P66,Sheet1!B1:B50,11,FALSE)


and even from another workbook:


=VLOOKUP(P66,'[Seminar Schedule.xlsx]Sheet1'!$B1:$B500,11,FALSE)


I get a #REF! error

 

[NARAYANK991]

Hi ,


Use something like this :


=INDEX(Sheet1!K:K,SMALL(IF(Sheet1!B:B="P0227",ROW(Sheet1!B:B)),1))


entered as an array formula , using CTRL SHIFT ENTER.


I have not understood the part about field P62 on the main tab ; if it is just cell P62 on the main tab , then you can include it in the above formula as follows :


=INDEX(Sheet1!K:K,SMALL(IF(Sheet1!B:B="P0227",IF(Sheet1!B:B=main!P62,ROW(Sheet1!B:B))),1))


again entered as an array formula.


Use the $ signs as appropriate.


Narayan