Matching two peices of data in the same row

drr3761 · May 23rd, 2002, 10:46 AM

I'm experimenting with syntax that would allow me to match two peices of data in the same row.

Here's an example:
I want to match a string in column A, but column A will have redundancies of that string. After finding a match in column A, I want to match a string in column B of that row. If there is a match in column A AND B, then output column C, otherwise find the next match in A and test for a match in column B of that row again.

Could someone helpe me out?
I'm sure I need a combination of OFFSET, AND, and perhaps index/lookup/match functions, but confused as to their order.

PLEASE HELP!!!

Jay Petrulis · May 23rd, 2002, 10:59 AM

Here is one way to do it,

=INDEX($1:$1000,MATCH("Red"&"Blue",A1:A1000&B1:B1000,0),3)

This must be array entered with Ctrl-Shift-Enter.

Aladin Akyurek · May 23rd, 2002, 11:09 AM

What follows is my reply (here slightly edited) to a question similar to yours which has been recently posted at the newsgroup: microsoft.public.excel.worksheet.functions.

Three alternative formulas for effecting a multikey lookup:

[1]

=INDEX($C$2:$C$5, MATCH(E1 & "-@-" & F1, $A$2:$A$5 &"-@-" & $B$2:$B$5, 0))

[2]

=INDEX($C$2:$C$5,MATCH(E1,IF($B$2:$B$5=F1,$A$2:$A$5),0))

[3]

=INDEX($C$2:$C$5, SUMPRODUCT(MATCH(E1 & "-@-" & F1, $A$2:$A$5 & "-@-" &
$B$2:$B$5, 0)))

where E1 houses a value to be matched against the values in A and F1 a value to be matched against the values in B.

[1] and [2] must be array-entered (that is, use control+shift+enter at the same time, not just enter).

If you are going to copy one of these formulas to a huge number of cells, they will slow down
your workbook.

It's better to trade off space (memory) against time (speed) in the latter situation. I'd suggest inserting a new column after your second column and use the following formula in that column which must be copied down:

=A2 & "-@-" B2

When you do a lookup, use either:

=VLOOKUP(E1 & "-@-" & F1, $C$2:$D$5,2,0)

or, preferably,

=INDEX($D$2:$D$5, MATCH(E1 & "-@-" & F1, $C$2:$C$5,0))

which should be faster.

I assumed throughout an original sample in A2:C5.

Aladin

AlexH342 · Jul 24th, 2002, 09:52 AM

HI THANKS FOR ALL THE HELP

I HAVE TO ADMIT I COULDN'T FATHOM THE LAST COUPLE OF POSTS.

IN THE END WHAT I DID WAS COMBINE THE TWO FIELDS INTO A HIDDEN COLUMN BY USING

=E52&" "&F52

I THEN USED THE SIMPLER (FOR ME ANYWAY)
VLOOKUP AS STATED IN EARLIER POSTS

THANKS ONCE AGAIN FOR ALL THE HELP

ALEX

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 23rd, 2002, 10:46 AM	#1
drr3761 New Member Join Date: May 2002 Posts: 1	I'm experimenting with syntax that would allow me to match two peices of data in the same row. Here's an example: I want to match a string in column A, but column A will have redundancies of that string. After finding a match in column A, I want to match a string in column B of that row. If there is a match in column A AND B, then output column C, otherwise find the next match in A and test for a match in column B of that row again. Could someone helpe me out? I'm sure I need a combination of OFFSET, AND, and perhaps index/lookup/match functions, but confused as to their order. PLEASE HELP!!!

May 23rd, 2002, 10:59 AM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Here is one way to do it, =INDEX($1:$1000,MATCH("Red"&"Blue",A1:A1000&B1:B1000,0),3) This must be array entered with Ctrl-Shift-Enter.

May 23rd, 2002, 11:09 AM	#3
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	What follows is my reply (here slightly edited) to a question similar to yours which has been recently posted at the newsgroup: microsoft.public.excel.worksheet.functions. Three alternative formulas for effecting a multikey lookup: [1] =INDEX($C$2:$C$5, MATCH(E1 & "-@-" & F1, $A$2:$A$5 &"-@-" & $B$2:$B$5, 0)) [2] =INDEX($C$2:$C$5,MATCH(E1,IF($B$2:$B$5=F1,$A$2:$A$5),0)) [3] =INDEX($C$2:$C$5, SUMPRODUCT(MATCH(E1 & "-@-" & F1, $A$2:$A$5 & "-@-" & $B$2:$B$5, 0))) where E1 houses a value to be matched against the values in A and F1 a value to be matched against the values in B. [1] and [2] must be array-entered (that is, use control+shift+enter at the same time, not just enter). If you are going to copy one of these formulas to a huge number of cells, they will slow down your workbook. It's better to trade off space (memory) against time (speed) in the latter situation. I'd suggest inserting a new column after your second column and use the following formula in that column which must be copied down: =A2 & "-@-" B2 When you do a lookup, use either: =VLOOKUP(E1 & "-@-" & F1, $C$2:$D$5,2,0) or, preferably, =INDEX($D$2:$D$5, MATCH(E1 & "-@-" & F1, $C$2:$C$5,0)) which should be faster. I assumed throughout an original sample in A2:C5. Aladin

Jul 24th, 2002, 09:52 AM	#4
AlexH342 New Member Join Date: Jul 2002 Location: Alex Posts: 7	HI THANKS FOR ALL THE HELP I HAVE TO ADMIT I COULDN'T FATHOM THE LAST COUPLE OF POSTS. IN THE END WHAT I DID WAS COMBINE THE TWO FIELDS INTO A HIDDEN COLUMN BY USING =E52&" "&F52 I THEN USED THE SIMPLER (FOR ME ANYWAY) VLOOKUP AS STATED IN EARLIER POSTS THANKS ONCE AGAIN FOR ALL THE HELP ALEX