Very Tricky Excel Lookup Across Workbooks

  • Thread starter Thread starter Chris
  • Start date Start date
C

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
 
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
 
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 :)
 
Back
Top