Spreadsheet Query

[matthewparry45]

Here is my problem:

In a single spreadhseet I have two tabs.

Tab number 1 has a series of names (a1:a100) with numbers in adjoining columns (b1:b100, c1:c100, d1:d100, etc.) that are directly related to the names in column A. (That is, information in B1, C1 and D1 all relate only to the unique name contained in cell A1. No names are repeated in the A column).

In tab number 2, I have a spreadhseet that extrapolates data from Tab 1. For example, if I type the name "John" in cell A10 of Tab 2, the data that relates to "John" on Tab 1 (cells B1, C1 and D1) appear in B10, C10 and D10.

What I need now is this:

A formula for a different spreadsheet inside Tab 2 that will scan the range of cells in which the name "John" could appear, then display "John" and all the corresponding information (cells B10, C10 and D10).

For example, if on Tab 1 I have this information:
A1 "John"
B1 "3"
C1 "cheese"
D1 "pizza"

And I type "John" in cell A10 of Tab 2, then the following information appears:

B10 "3"
C10 "cheese"
D10 "pizza"

What I need is a formula for Tab 2 that will scan a given range (A1:A100) in Tab 1 for a match of what is typed in cell A10 of Tab 2, then display that text (in this case, "John").

I have tried "SUMIF" and "(IF(AND" formulae, but I cannot figure this out.

Any help would be appreciated - I can e-mail the spreadsheet with better details if anyone has the time to look at it.

THANKS!!

Matthew

 

[PaddyD]

Hi - welcome to the board.

The function you need is vlookup. Check it out in the help files, and see here:


http://mrexcel.com/board/viewtopic.php?topic=20292&forum=2&start=0


for an example of how you can use just one vlookup formula to return multiple results from different columns.

Post back if you need more...

Paddy

 

[matthewparry45]

Paddy,

Thanks for your incredibly quick response. I tried the VLOOKUP formula but it did not give me the information I needed, possibly because what I am trying to accomplish cannot be done in Excel (?). I will continue to plod away at this, but thanks anyway!

Matthew

 

[PaddyD]

From your description it should be do-able...what went wrong when you tried the vlookup?

Paddy