Show data in 2 different ways without having to input cell refs manually?

Originally Posted by annieq

I would like to find a way of having 2 different worksheets with the same data, but presented in 2 different ways, and I would like not to have to input the cell references manually.

So I would like to find an Excel translation of this English statement (for example):

Look on worksheet 1 column B and find “abc”
What is the numerical value in column E, same row as “abc”?
Put this value here.

I guess ideally, I would like to be able to generalise this:

What is the text on this sheet in column B?
Find this text in column B on worksheet 1.
What is the numerical value in column E, worksheet 1, same row?
Put this value here.

But for now a solution to the first would be great!

Hi,

something like

=VLookup("abc",Sheet1!B:E,4,False)

and

=VLookup(B1,Sheet1!B:E,4,False)

should do that for you.

hth
---

The '4' in that formula refers to the column number to retrieve from the lookup range. In the formula, the lookup range is 'B:E' (column B through E). The '4' returns the value in the fourth column, which is column E.

Had you wanted to retrieve the value in column C, that 4 would change to a 2 in this instance.