#### matthewparry45

##### New Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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

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

Replies
1
Views
214
Replies
4
Views
377
Replies
2
Views
878
Replies
3
Views
544
Replies
0
Views
257

1,203,189
Messages
6,054,014
Members
444,696
Latest member
VASUCH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back