VLOOKUP from multiple table_arrays?

I have what probably should be in one many-rowed table broken into three
separate tables side by side just so I can see them more easily.


......a.................b........c.......d........ ....e........f......g.............h
1..Phillip.........90..............Andy......10... .........Steward..6
2..Rebecca.....24..............Angela...10........ ....Greg.......5
3..Tracy..........20..............Sam.......10
4..Keith...........20


I want to enter a name and have the number associated with that name
returned. That would be easy with the vlookup if it were all in one
table_array {vlookup("tracy",a1:b4,2)} but how do I do it with the data
broken up? Is there a way to keep the visual ease like I have it and
reference multiple ranges?

Is there a way to keep the visual ease like I have it
and reference multiple ranges?

A sequential index/match would be one way
Assume lookup values entered in H2 down, eg: Tracy
Then in I2:
=IF(ISNA(MATCH(H2,A:A,0)),IF(ISNA(MATCH(H2,C:C,0)) ,IF(ISNA(MATCH(H2,E:E,0)),"",INDEX(F:F,MATCH(H2,E: E,0))),INDEX(D,MATCH(H2,C:C,0))),INDEX(B:B,MATCH (H2,A:A,0)))
Copy I2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---
"Seebs" wrote:
I have what probably should be in one many-rowed table broken into three
separate tables side by side just so I can see them more easily.


.....a.................b........c.......d......... ...e........f......g.............h
1..Phillip.........90..............Andy......10... .........Steward..6
2..Rebecca.....24..............Angela...10........ ....Greg.......5
3..Tracy..........20..............Sam.......10
4..Keith...........20


I want to enter a name and have the number associated with that name
returned. That would be easy with the vlookup if it were all in one
table_array {vlookup("tracy",a1:b4,2)} but how do I do it with the data
broken up? Is there a way to keep the visual ease like I have it and
reference multiple ranges?

Hi,

You can try the following:

1. Assign names - Name range A1:B4 as First, D1:E3 as Second and G1:H2 as
Third
2. In cell A7, type First and in cell A8, type Greg
3. In cell D7, type the following formula
=IF(ISERROR(VLOOKUP(A8,INDIRECT(A7),2,0)),"",VLOOK UP(A8,INDIRECT(A7),2,0))
4. In cell D810, type Greg, Rebecca, Keith
5. In E7:G7, type First, Second, Third
6. Now highlight range D7:G10
7. Go to Data Table
8. In column input cell, give the reference of cell A8
9. In row input cell, give the reference of cell A7

The table should now get populated with all the correct figures. Please
feel free to extend the list of names in range D810 and run the data table
with all rows.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Seebs" wrote in message
...
I have what probably should be in one many-rowed table broken into three
separate tables side by side just so I can see them more easily.


.....a.................b........c.......d......... ...e........f......g.............h
1..Phillip.........90..............Andy......10... .........Steward..6
2..Rebecca.....24..............Angela...10........ ....Greg.......5
3..Tracy..........20..............Sam.......10
4..Keith...........20


I want to enter a name and have the number associated with that name
returned. That would be easy with the vlookup if it were all in one
table_array {vlookup("tracy",a1:b4,2)} but how do I do it with the data
broken up? Is there a way to keep the visual ease like I have it and
reference multiple ranges?

This will only work if the value to be returned is numeric as is shown in
your sample data:

A10 = lookup name = Tracy

=SUMIF(A1:E4,A10,B1:F4)

--
Biff
Microsoft Excel MVP


"Seebs" wrote in message
...
I have what probably should be in one many-rowed table broken into three
separate tables side by side just so I can see them more easily.


.....a.................b........c.......d......... ...e........f......g.............h
1..Phillip.........90..............Andy......10... .........Steward..6
2..Rebecca.....24..............Angela...10........ ....Greg.......5
3..Tracy..........20..............Sam.......10
4..Keith...........20


I want to enter a name and have the number associated with that name
returned. That would be easy with the vlookup if it were all in one
table_array {vlookup("tracy",a1:b4,2)} but how do I do it with the data
broken up? Is there a way to keep the visual ease like I have it and
reference multiple ranges?