Match criteria and return text from another cell

dacowgirl0102 · Dec 30, 2003

I'm trying to perform an exact match from sheet1 $D2 to Sheet2 $F:$F, sheet1 $B2 to Sheet2 $D:$D, and sheet1 $E2 to Sheet2 $H:$H in order to return sheet2 $Z:$Z. Sheet1's D2 and E2 are numbers, B2 is a letter, and Z should return something like W 14 X 22#

Here are some formulas that I've tried:

=LOOKUP("$D2 AND $E2","'Heat Summary'!$F:$F AND 'Heat Summary'!$G:$G",'Heat Summary'!$Z:$Z) I get a #Value! error

=LOOKUP($D2,'Heat Summary'!$F$1:$F$366,'Heat Summary'!$Z$1:$Z$366) This one returns a value, but the wrong one

=SUMPRODUCT(('Heat Summary'!$F$1:$F$366=$D2)*('Heat Summary'!$D$1:$D$366=$B2)*('Heat Summary'!$H$1:$H$366=$E2)*('Heat Summary'!$Z$1:$Z$366)) And I get #Value! again.

Any help would be greatly appriciated.

Aladin Akyurek · Dec 30, 2003

In which row does the data in Sheet2 starts? Is column AA in Sheet2 free?

dacowgirl0102 · Dec 30, 2003

Column headings are in row1 and actual data starts in row 2 of sheet2. there are 366 rows of data in sheet 2, but as time goes by, I'll be adding more rows, so having an indifinite number of rows is preferable. Yes AA is free.

Aladin Akyurek · Dec 30, 2003

dacowgirl0102 said:
Column headings are in row1 and actual data starts in row 2 of sheet2. there are 366 rows of data in sheet 2, but as time goes by, I'll be adding more rows, so having an indifinite number of rows is preferable. Yes AA is free.

In AA1 enter: Concat

In AA2 enter & copy down:

=F2&CHAR(127)&D2&CHAR(127)&H2

Now try:

INDEX('Heat Summary'!$Z:$Z,MATCH($D2&CHAR(127)&$B2&CHAR(127)&$E2,'Heat Summary'!$AA:$AA,0))

dacowgirl0102 · Dec 30, 2003

Awsome! Thanks Bunches

Dixie

Yogi Anand · Dec 30, 2003

Hi dacogirl:

In addition to valuable contribution from Aladin -- and that is the way to go -- I have tried to look at your SUMPRODUCT formulation ...

I noticed that in your formula, you have used 'Heat Summary' sheet in place of Sheet2 in your narrative description.

If I have understood correctly what you are trying to do in reference to your SUMPRODUCT formulation, I can make it work with the following modification to your formula ...

=INDEX('Heat Summary'!$Z$1:$Z$366,SUMPRODUCT(('Heat Summary'!$F$1:$F$366=$D2)*('Heat Summary'!$D$1:$D$366=$B2)*('Heat Summary'!$H$1:$H$366=$E2)*ROW($A$1:$A$366)))

Aladin Akyurek · Dec 30, 2003

Yogi Anand said:
Hi dacogirl:

I noticed that in your formula, you have used 'Heat Summary' sheet in place of Sheet2 in your narrative description.

If I have understood correctly what you are trying to do in reference to your SUMPRODUCT formulation, I can make it work with the following modification to your formula ...

=INDEX('Heat Summary'!$Z$1:$Z$366,SUMPRODUCT(('Heat Summary'!$F$1:$F$366=$D2)*('Heat Summary'!$D$1:$D$366=$B2)*('Heat Summary'!$H$1:$H$366=$E2)*ROW($A$1:$A$366)))

as shown in the following illustration ...

Among a few disadvantages of such a misplaced use of SumProduct for retrieval purposes, here the obvious inefficiency will be most appealing.

Yogi Anand · Dec 30, 2003

Hi Aladin:

Yes Sir -- I fully agree!

Match criteria and return text from another cell

dacowgirl0102

New Member

Excel Facts

Aladin Akyurek

MrExcel MVP

dacowgirl0102

New Member

Aladin Akyurek

MrExcel MVP

dacowgirl0102

New Member

Yogi Anand

MrExcel MVP

Aladin Akyurek

MrExcel MVP

Yogi Anand

MrExcel MVP

Similar threads

Forum statistics

Share this page

Match criteria and return text from another cell

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock