Complicated Lookup
 

There is to be a lot of discussion on multiple column lookups but they are
exact matches and I just can't wrap my head around this one.

I have 2 text values in C3 and C4.

C3 needs to exactly match a value from column D, E, F or G. (column headings
at row 10, so data from 11 down).

C4 must be found within a list of CSVs in each cell in column H. (column
headings at row 10, so data from 11 down).

I then need the value under the heading in Column C that matches the same row.

--
Trefor

Complicated Lookup
 

Sorry I meant to add that I only need to get the C4 match if there is more
than one match for C3. So if the C3 match is unique that is good enough.

Any help at all would really be appreciated.

--
Trefor


"Trefor" wrote:

There is to be a lot of discussion on multiple column lookups but they are
exact matches and I just can't wrap my head around this one.

I have 2 text values in C3 and C4.

C3 needs to exactly match a value from column D, E, F or G. (column headings
at row 10, so data from 11 down).

C4 must be found within a list of CSVs in each cell in column H. (column
headings at row 10, so data from 11 down).

I then need the value under the heading in Column C that matches the same row.

--
Trefor

Complicated Lookup
 

You can use COUNTIF to see if there is more than one match between C3
and the data in columns D E F or G, so you could try something like
this:

=IF(COUNTIF(D11:G100,C3)=0,"Not
present",IF(COUNTIF(D11:G100,C3)1,INDEX(C11:C100, MATCH(C4,H11:H100,0)),INDEX(C11:C100,IF(ISNA(MATCH (C3,D11:D100,0)),IF(ISNA(MATCH(C3,E11:E100,0)),IF( ISNA(MATCH(C3,F11:F100,0)),MATCH(C3,G11:G100,0),MA TCH(C3,F11:F100,0)),MATCH(C3,E11:E100,0)),MATCH(C3 ,D11:D100,0)))))

All one formula - be wary of spurious line breaks in the newsgroups.

Hope this helps.

Pete

On Jul 7, 11:16*am, Trefor wrote:
Sorry I meant to add that I only need to get the C4 match if there is more
than one match for C3. So if the C3 match is unique that is good enough.

Any help at all would really be appreciated.

--
Trefor



"Trefor" wrote:
There is to be a lot of discussion on multiple column lookups but they are
exact matches and I just can't wrap my head around this one.

I have 2 text values in C3 and C4.

C3 needs to exactly match a value from column D, E, F or G. (column headings
at row 10, so data from 11 down).

C4 must be found within a list of CSV’s in each cell in column H. (column
headings at row 10, so data from 11 down).

I then need the value under the heading in Column C that matches the same row.

--
Trefor- Hide quoted text -

- Show quoted text -

Complicated Lookup
 

I forgot to say that I've assumed that you have data to row 100 -
change all instances of 100 if you have more rows.

Hope this helps.

Pete

On Jul 7, 12:04*pm, Pete_UK wrote:
You can use COUNTIF to see if there is more than one match between C3
and the data in columns D E F or G, so you could try something like
this:

=IF(COUNTIF(D11:G100,C3)=0,"Not
present",IF(COUNTIF(D11:G100,C3)1,INDEX(C11:C100, MATCH(C4,H11:H100,0)),IND*EX(C11:C100,IF(ISNA(MATC H(C3,D11:D100,0)),IF(ISNA(MATCH(C3,E11:E100,0)),IF (*ISNA(MATCH(C3,F11:F100,0)),MATCH(C3,G11:G100,0), MATCH(C3,F11:F100,0)),MATCH*(C3,E11:E100,0)),MATCH (C3,D11:D100,0)))))

All one formula - be wary of spurious line breaks in the newsgroups.

Hope this helps.

Pete

On Jul 7, 11:16*am, Trefor wrote:



Sorry I meant to add that I only need to get the C4 match if there is more
than one match for C3. So if the C3 match is unique that is good enough..

Any help at all would really be appreciated.

--
Trefor

"Trefor" wrote:
There is to be a lot of discussion on multiple column lookups but they are
exact matches and I just can't wrap my head around this one.

I have 2 text values in C3 and C4.

C3 needs to exactly match a value from column D, E, F or G. (column headings
at row 10, so data from 11 down).

C4 must be found within a list of CSV’s in each cell in column H. (column
headings at row 10, so data from 11 down).

I then need the value under the heading in Column C that matches the same row.

--
Trefor- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Complicated Lookup
 

In C10:G10 I have the letters a,b,c,.....
In H11,H20 I have aa,bb,cc,dd,ee,ff,....
In C3 I have: b
In C4 I have: dd
In D3 I use =MATCH(C3,C10:G10,0) this returns 2 since "b" is the second
entry in C10:G10
In D4 I use =MATCH(C4,H11:H20,0) this returns 4 since "dd" is the fourth
entry in H11:H20
In D5 I used =INDEX(C11:G20,D4,D3) to locate the item in row 4, column 2

I could combine this to one formula
=INDEX(C11:G20,MATCH(C4,H11:H100,0),MATCH(C3,C10:G 10,0))

Not so complicated after all <grin
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Trefor" wrote in message
...
There is to be a lot of discussion on multiple column lookups but they are
exact matches and I just can't wrap my head around this one.

I have 2 text values in C3 and C4.

C3 needs to exactly match a value from column D, E, F or G. (column
headings
at row 10, so data from 11 down).

C4 must be found within a list of CSV's in each cell in column H. (column
headings at row 10, so data from 11 down).

I then need the value under the heading in Column C that matches the same
row.

--
Trefor

Complicated Lookup
 

Pete,

Many thanks for the reply, this is very close, but something is broken.

C3 = "FUJITSU AUSTRALIA LTD"
C4 = "NSW"
Your formula is in D4 and = "Di Data - NSW" - Which is wrong! But every
other possible configuration for C3 works perfectly, it does not make any
sense, but then I am still trying to get to grips with you formula.

C11 - C14
Di Data - NSW
Di Data - VIC, SA, WA
Fujitsu Aust (North)
Fujitsu Aust (South)

D11 - D14
DIMENSION DATA AUSTRALIA PTY LTD
DIMENSION DATA AUSTRALIA PTY LTD
FUJITSU AUSTRALIA
FUJITSU AUSTRALIA

E11 - E14
DIMENSION DATA
DIMENSION DATA
FUJITSU AUSTRALIA LIMITED
FUJITSU AUSTRALIA LIMITED

F13 - F14
FUJITSU AUSTRALIA LTD
FUJITSU AUSTRALIA LTD

G11 - G14 - blank

H11 - H14
NSW
VIC, SA, WA
QLD, NSW, NT, ACT
VIC, WA, SA, TAS



--
Trefor


"Pete_UK" wrote:

I forgot to say that I've assumed that you have data to row 100 -
change all instances of 100 if you have more rows.

Hope this helps.

Pete

On Jul 7, 12:04 pm, Pete_UK wrote:
You can use COUNTIF to see if there is more than one match between C3
and the data in columns D E F or G, so you could try something like
this:

=IF(COUNTIF(D11:G100,C3)=0,"Not
present",IF(COUNTIF(D11:G100,C3)1,INDEX(C11:C100, MATCH(C4,H11:H100,0)),INDÂ*EX(C11:C100,IF(ISNA(MAT CH(C3,D11:D100,0)),IF(ISNA(MATCH(C3,E11:E100,0)),I F(Â*ISNA(MATCH(C3,F11:F100,0)),MATCH(C3,G11:G100,0 ),MATCH(C3,F11:F100,0)),MATCHÂ*(C3,E11:E100,0)),MA TCH(C3,D11:D100,0)))))

All one formula - be wary of spurious line breaks in the newsgroups.

Hope this helps.

Pete

On Jul 7, 11:16 am, Trefor wrote:



Sorry I meant to add that I only need to get the C4 match if there is more
than one match for C3. So if the C3 match is unique that is good enough..

Any help at all would really be appreciated.

--
Trefor

"Trefor" wrote:
There is to be a lot of discussion on multiple column lookups but they are
exact matches and I just can't wrap my head around this one.

I have 2 text values in C3 and C4.

C3 needs to exactly match a value from column D, E, F or G. (column headings
at row 10, so data from 11 down).

C4 must be found within a list of CSVs in each cell in column H. (column
headings at row 10, so data from 11 down).

I then need the value under the heading in Column C that matches the same row.

--
Trefor- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Complicated Lookup
 

Bernard,

I just sent an update to Pete with all the data. If I simply replace Pete's
formula with your formula I get #N/A.

C10:G10 in my sheet are column headers, not data. Sorry I am missing
something here.

--
Trefor


"Bernard Liengme" wrote:

In C10:G10 I have the letters a,b,c,.....
In H11,H20 I have aa,bb,cc,dd,ee,ff,....
In C3 I have: b
In C4 I have: dd
In D3 I use =MATCH(C3,C10:G10,0) this returns 2 since "b" is the second
entry in C10:G10
In D4 I use =MATCH(C4,H11:H20,0) this returns 4 since "dd" is the fourth
entry in H11:H20
In D5 I used =INDEX(C11:G20,D4,D3) to locate the item in row 4, column 2

I could combine this to one formula
=INDEX(C11:G20,MATCH(C4,H11:H100,0),MATCH(C3,C10:G 10,0))

Not so complicated after all <grin
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Trefor" wrote in message
...
There is to be a lot of discussion on multiple column lookups but they are
exact matches and I just can't wrap my head around this one.

I have 2 text values in C3 and C4.

C3 needs to exactly match a value from column D, E, F or G. (column
headings
at row 10, so data from 11 down).

C4 must be found within a list of CSV's in each cell in column H. (column
headings at row 10, so data from 11 down).

I then need the value under the heading in Column C that matches the same
row.

--
Trefor

Complicated Lookup
 

Well, as I understand your problem, you have "Fujitsu Australia Ltd"
appearing more than once in columns D to G, so in this instance you
want to match what is in C4 with column H. "NSW" is in the first row
of column H (below your headings), so it matches with C11 - "Di Data -
NSW".

What would you expect it to show?

Pete

On Jul 7, 2:45*pm, Trefor wrote:
Pete,

Many thanks for the reply, this is very close, but something is broken.

C3 = "FUJITSU AUSTRALIA LTD"
C4 = "NSW"
Your formula is in D4 and = "Di Data - NSW" - Which is wrong! But every
other possible configuration for C3 works perfectly, it does not make any
sense, but then I am still trying to get to grips with you formula.

C11 - C14
Di Data - NSW
Di Data - VIC, SA, WA
Fujitsu Aust (North)
Fujitsu Aust (South)

D11 - D14
DIMENSION DATA AUSTRALIA PTY LTD
DIMENSION DATA AUSTRALIA PTY LTD
FUJITSU AUSTRALIA
FUJITSU AUSTRALIA

E11 - E14
DIMENSION DATA
DIMENSION DATA
FUJITSU AUSTRALIA LIMITED
FUJITSU AUSTRALIA LIMITED

F13 - F14
FUJITSU AUSTRALIA LTD
FUJITSU AUSTRALIA LTD

G11 - G14 - blank

H11 - H14
NSW
VIC, SA, WA
QLD, NSW, NT, ACT
VIC, WA, SA, TAS

--
Trefor

Complicated Lookup
 

Try this in say C5, array-entered*:
=IF(ISNA(MATCH(1,(ISNUMBER(SEARCH(C3,D11:D14)))*(I SNUMBER(SEARCH(C4,H11:H14))),0)),IF(ISNA(MATCH(1,( ISNUMBER(SEARCH(C3,E11:E14)))*(ISNUMBER(SEARCH(C4, H11:H14))),0)),IF(ISNA(MATCH(1,(ISNUMBER(SEARCH(C3 ,F11:F14)))*(ISNUMBER(SEARCH(C4,H11:H14))),0)),"", INDEX(C11:C14,MATCH(1,(ISNUMBER(SEARCH(C3,F11:F14) ))*(ISNUMBER(SEARCH(C4,H11:H14))),0))),INDEX(C11:C 14,MATCH(1,(ISNUMBER(SEARCH(C3,E11:E14)))*(ISNUMBE R(SEARCH(C4,H11:H14))),0))),INDEX(C11:C14,MATCH(1, (ISNUMBER(SEARCH(C3,D11:D14)))*(ISNUMBER(SEARCH(C4 ,H11:H14))),0)))

*Array-enter = Press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Trefor" wrote:
C3 = "FUJITSU AUSTRALIA LTD"
C4 = "NSW"

C11 - C14
Di Data - NSW
Di Data - VIC, SA, WA
Fujitsu Aust (North)
Fujitsu Aust (South)

D11 - D14
DIMENSION DATA AUSTRALIA PTY LTD
DIMENSION DATA AUSTRALIA PTY LTD
FUJITSU AUSTRALIA
FUJITSU AUSTRALIA

E11 - E14
DIMENSION DATA
DIMENSION DATA
FUJITSU AUSTRALIA LIMITED
FUJITSU AUSTRALIA LIMITED

F13 - F14
FUJITSU AUSTRALIA LTD
FUJITSU AUSTRALIA LTD

G11 - G14 - blank

H11 - H14
NSW
VIC, SA, WA
QLD, NSW, NT, ACT
VIC, WA, SA, TAS

Complicated Lookup
 

Pete,

I am expecting it to give me what is in C13.

So if C3 = something in (D or E or F or G) AND H then result = C of the same
row.

I think Max may have cracked what I need, so many thanks for your help.

--
Trefor


"Pete_UK" wrote:

Well, as I understand your problem, you have "Fujitsu Australia Ltd"
appearing more than once in columns D to G, so in this instance you
want to match what is in C4 with column H. "NSW" is in the first row
of column H (below your headings), so it matches with C11 - "Di Data -
NSW".

What would you expect it to show?

Pete

On Jul 7, 2:45 pm, Trefor wrote:
Pete,

Many thanks for the reply, this is very close, but something is broken.

C3 = "FUJITSU AUSTRALIA LTD"
C4 = "NSW"
Your formula is in D4 and = "Di Data - NSW" - Which is wrong! But every
other possible configuration for C3 works perfectly, it does not make any
sense, but then I am still trying to get to grips with you formula.

C11 - C14
Di Data - NSW
Di Data - VIC, SA, WA
Fujitsu Aust (North)
Fujitsu Aust (South)

D11 - D14
DIMENSION DATA AUSTRALIA PTY LTD
DIMENSION DATA AUSTRALIA PTY LTD
FUJITSU AUSTRALIA
FUJITSU AUSTRALIA

E11 - E14
DIMENSION DATA
DIMENSION DATA
FUJITSU AUSTRALIA LIMITED
FUJITSU AUSTRALIA LIMITED

F13 - F14
FUJITSU AUSTRALIA LTD
FUJITSU AUSTRALIA LTD

G11 - G14 - blank

H11 - H14
NSW
VIC, SA, WA
QLD, NSW, NT, ACT
VIC, WA, SA, TAS

--
Trefor

Complicated Lookup
 

Max,

Looks good, not sure I understand what you have done, but this is a once off
and my problem is fixed so I am happy ;)

Many thanks again.

--
Trefor


"Max" wrote:

Try this in say C5, array-entered*:
=IF(ISNA(MATCH(1,(ISNUMBER(SEARCH(C3,D11:D14)))*(I SNUMBER(SEARCH(C4,H11:H14))),0)),IF(ISNA(MATCH(1,( ISNUMBER(SEARCH(C3,E11:E14)))*(ISNUMBER(SEARCH(C4, H11:H14))),0)),IF(ISNA(MATCH(1,(ISNUMBER(SEARCH(C3 ,F11:F14)))*(ISNUMBER(SEARCH(C4,H11:H14))),0)),"", INDEX(C11:C14,MATCH(1,(ISNUMBER(SEARCH(C3,F11:F14) ))*(ISNUMBER(SEARCH(C4,H11:H14))),0))),INDEX(C11:C 14,MATCH(1,(ISNUMBER(SEARCH(C3,E11:E14)))*(ISNUMBE R(SEARCH(C4,H11:H14))),0))),INDEX(C11:C14,MATCH(1, (ISNUMBER(SEARCH(C3,D11:D14)))*(ISNUMBER(SEARCH(C4 ,H11:H14))),0)))

*Array-enter = Press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Trefor" wrote:
C3 = "FUJITSU AUSTRALIA LTD"
C4 = "NSW"

C11 - C14
Di Data - NSW
Di Data - VIC, SA, WA
Fujitsu Aust (North)
Fujitsu Aust (South)

D11 - D14
DIMENSION DATA AUSTRALIA PTY LTD
DIMENSION DATA AUSTRALIA PTY LTD
FUJITSU AUSTRALIA
FUJITSU AUSTRALIA

E11 - E14
DIMENSION DATA
DIMENSION DATA
FUJITSU AUSTRALIA LIMITED
FUJITSU AUSTRALIA LIMITED

F13 - F14
FUJITSU AUSTRALIA LTD
FUJITSU AUSTRALIA LTD

G11 - G14 - blank

H11 - H14
NSW
VIC, SA, WA
QLD, NSW, NT, ACT
VIC, WA, SA, TAS

Complicated Lookup
 

Welcome, glad it worked out ok for you.

.. not sure I understand what you have done
Here's some easy explanations to help:

Indicatively, the collapsed expression is simply this:
=IF(ISNA(1),IF(ISNA(2),IF(ISNA(3),"",INDEX(3)),IND EX(2)),INDEX(1)))

It's essentially a sequential, dual criteria index/match using the 2 inputs
that you have in C3 and C4. The matching sequence (read from left to right
in the collapsed expression) is: Match C3/C4 vs Cols D & H then vs Cols E
& H then vs Cols F & H. And where it matches the dual criteria (the first
matching instance), extract corresponding value from col C (C11:C14).

This part of it, eg:
(ISNUMBER(SEARCH(C3,D11:D14)))*(ISNUMBER(SEARCH(C4 ,H11:H14)
resolves to an array of 1's/0's depending on where the dual criteria** is
satisfied (1's) or not (0's), eg: {0;0;1;0}
**ie check where D11:D14 contains C3 AND H11:H14 contains C4

MATCH(1,{0;0;1;0},0) then returns the position of the 1st/single "1" within
the array, eg over he 3

This position: 3 is then used to extract the corresponding value from the
INDEX(C11:C14, ...), viz it'll return the 3rd element from C11:C14, ie
what's in C13
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Trefor" wrote in message
...
Max,

Looks good, not sure I understand what you have done, but this is a once
off
and my problem is fixed so I am happy ;)

Many thanks again.

--
Trefor

Complicated Lookup
 

Max or anyone that can understand this,

The forumlae you did for me will give me a value in another column within
the array. Is it possible for the result to be the row within the array? That
way I calculate the row once and can then use the row number as a reference
to pull data from other columns in the array/table.

--
Trefor


"Max" wrote:

Welcome, glad it worked out ok for you.

.. not sure I understand what you have done
Here's some easy explanations to help:

Indicatively, the collapsed expression is simply this:
=IF(ISNA(1),IF(ISNA(2),IF(ISNA(3),"",INDEX(3)),IND EX(2)),INDEX(1)))

It's essentially a sequential, dual criteria index/match using the 2 inputs
that you have in C3 and C4. The matching sequence (read from left to right
in the collapsed expression) is: Match C3/C4 vs Cols D & H then vs Cols E
& H then vs Cols F & H. And where it matches the dual criteria (the first
matching instance), extract corresponding value from col C (C11:C14).

This part of it, eg:
(ISNUMBER(SEARCH(C3,D11:D14)))*(ISNUMBER(SEARCH(C4 ,H11:H14)
resolves to an array of 1's/0's depending on where the dual criteria** is
satisfied (1's) or not (0's), eg: {0;0;1;0}
**ie check where D11:D14 contains C3 AND H11:H14 contains C4

MATCH(1,{0;0;1;0},0) then returns the position of the 1st/single "1" within
the array, eg over he 3

This position: 3 is then used to extract the corresponding value from the
INDEX(C11:C14, ...), viz it'll return the 3rd element from C11:C14, ie
what's in C13
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Trefor" wrote in message
...
Max,

Looks good, not sure I understand what you have done, but this is a once
off
and my problem is fixed so I am happy ;)

Many thanks again.

--
Trefor

Complicated Lookup
 

Do you actually need to lookup each individual column or can you check and
array/table?

So my problem is this (using current cell references not what is in this
thread):

I have a table: C11:M47

In Columns D - G there is a list of names that I trying to find an exact
match with the value in C3. There could be multiple matches, so as a second
criteria I want to see if the value in C4 can be found (i.e. not an exact
match) in the column H11:H47.

If a double match is found, then I would like the row number or the row
within the table. With the row I will then select the data from various
columns in the table.

--
Trefor


"Trefor" wrote:

Max or anyone that can understand this,

The forumlae you did for me will give me a value in another column within
the array. Is it possible for the result to be the row within the array? That
way I calculate the row once and can then use the row number as a reference
to pull data from other columns in the array/table.

--
Trefor


"Max" wrote:

Welcome, glad it worked out ok for you.

.. not sure I understand what you have done
Here's some easy explanations to help:

Indicatively, the collapsed expression is simply this:
=IF(ISNA(1),IF(ISNA(2),IF(ISNA(3),"",INDEX(3)),IND EX(2)),INDEX(1)))

It's essentially a sequential, dual criteria index/match using the 2 inputs
that you have in C3 and C4. The matching sequence (read from left to right
in the collapsed expression) is: Match C3/C4 vs Cols D & H then vs Cols E
& H then vs Cols F & H. And where it matches the dual criteria (the first
matching instance), extract corresponding value from col C (C11:C14).

This part of it, eg:
(ISNUMBER(SEARCH(C3,D11:D14)))*(ISNUMBER(SEARCH(C4 ,H11:H14)
resolves to an array of 1's/0's depending on where the dual criteria** is
satisfied (1's) or not (0's), eg: {0;0;1;0}
**ie check where D11:D14 contains C3 AND H11:H14 contains C4

MATCH(1,{0;0;1;0},0) then returns the position of the 1st/single "1" within
the array, eg over he 3

This position: 3 is then used to extract the corresponding value from the
INDEX(C11:C14, ...), viz it'll return the 3rd element from C11:C14, ie
what's in C13
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Trefor" wrote in message
...
Max,

Looks good, not sure I understand what you have done, but this is a once
off
and my problem is fixed so I am happy ;)

Many thanks again.

--
Trefor