Find value in one column, display the text from adjacent colomn

**todley1** · 03-04-2010, 01:55 PM

How can I accomplish this?
The formula in cell A1 looks for the value in a cell in Column L which ranges from L1 to L75. This value will be found in Column Q also from 1 to 75. I then require the text found in the adjacent cell in Column R to be displayed. I also require that if there is no value entered in any cell in Column Q that the cell would remain blank.
Thanks

**Paul** · 03-04-2010, 02:02 PM

Hi Todley, welcome to the forum.

Can you put together a small example of your data/layout and show the expected results? You can upload small excel files directly to the thread.

I'm just not understanding why column L is being referenced.. and why there would be no values in column Q if you're trying to search that range.

**todley1** · 03-04-2010, 02:33 PM

I tried to use the IF function but found that I can only nest 7 items. I made a duplicate of the form I am trying to design which is a Work Schedule that covers 3 locations with 3 shifts each.
The object is to only have to input the employees pay number in the cell of the duplicate form and their name would display in the corresponding cell in the printable form. I have a feeling you are going to tell me that there is a much simpler solution.
thanks Paul

**Paul** · 03-04-2010, 02:46 PM

Is this what you're after? Person enters their employee # in L2 and consequently cell A2 is updated with their name (based on the values in Q:R)?

In A2:
=VLOOKUP(L2,$Q$2:$R$10,2,0)

To hide an error if no match was found:

Excel 2003 and earlier:
=IF(ISERROR(VLOOKUP(L2,$Q$2:$R$10,2,0)),"",VLOOKUP(L2,$Q$2:$R$10,2,0))

Excel 2007 and later:
=IFERROR(VLOOKUP(L2,$Q$2:$R$10,2,0),"")

**todley1** · 03-05-2010, 04:48 AM

Thanks Paul. I used the formula below that you submitted and it worked in one section but not in the others when I copied and pasted it in. It took me quite awhile to figure out why.
Turned out that the cells where it was working were formatted as text and the rest of the spreadsheet was formatted as numbers.
Thanks for the fast and accurate response.

Tod

Excel 2003 and earlier:
=IF(ISERROR(VLOOKUP(L2,$Q$2:$R$10,2,0)),"",VLOOKUP(L2,$Q$2:$R$10,2,0))