Very Tricky Excel Lookup Across Workbooks

[Chris]

Hello,

I'd like to perform a very tricky lookup (in my novice opinion).

The first step is that given 2 strings (in the item row) in 2
different columns like this:

| A | B |
Item |4567 AXE | 6843 FACE |

I am only concerned with the numbers.

I want to get the numbers separated from the text that I paste into
those cells.

So i would now have:
A B
Item | 4567 | 6843 |

The next step is that given those two numbers, I have another
worksheet that uses them as indexes. I want to look up a cell value
based on those indexes.


here's an example:

Indices.xls
| A | B | C | D | E |
1| BLANK | 1234 | 2135 | 4567 | 6843 |
2| 1234 | BLANK | MD | RG | ED |
3| 2135 | PR | BLANK | ZO | LP |
4| 4567 | FL | GW | BLANK | WZ |
5| 6843 | JZ | YR | XS | BLANK |
6|
7| continued...
note: this is about a 200x200 cell matrix, there are many of them i
use.

In summary, I want to get the numbers 4567 and 6843 out of the strings
I am given, plug them into some kind of lookup formula based on the
numbers in "indices.xls" and get "WZ" as a result.

Notes:
The numbers in "indices.xls" are in order already.

The string i copy has a space between the number and the item.
example: "4567 AXE" - the space could be used as a delimiting
character.

Thanks for your time,
Chris

 

[Domenic]

To seperate the number from text, you can use "Text to Columns", and us
space as your delimiter.

Once that's done, you can use the following formula:

=INDEX(Sheet1!$B$2:$E$5,MATCH(Sheet2!B1,Sheet1!$A$2:$A$5,0),MATCH(Sheet2!C1,Sheet1!$B$1:$E$1,0))

If you would prefer not to split the number and text, then you can us
the following formula:

=INDEX(Sheet1!$B$2:$E$5,MATCH(--LEFT(Sheet2!B1,FIND(
",Sheet2!B1)-1),Sheet1!$A$2:$A$5,0),MATCH(--LEFT(Sheet2!C1,FIND(
",Sheet2!C1)-1),Sheet1!$B$1:$E$1,0))

Assumes your table is in Sheet1

Sheet2!B1 contains the first number of interest

Sheet2!C1 contains the second number of interest

Hope this helps

 

[Chris]

Ok I'll try that stuff on Monday and let you know how it works, I'm
away from my office this weekend so i have to relax