ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Formula to Match Values (https://www.excelbanter.com/excel-worksheet-functions/213994-formula-match-values.html)

maperalia
Formula to Match Values
 
I wonder if you can help me to setup this formula.
I have values located in the excel cell as shown below:

A B C D E F G
1 bag 24 24 24 72 63 top
2 tube 24 24 21 69
3 cord 24 24 18 66
4 top 24 21 18 63
5 center 24 21 15 60
6 paper 24 21 12 57


Basically, what I am doing is:
1.- Taking the sum for each row (B+C+D).
2.- Compare and match values on the column "E" with the number located in
the cell €œ F1€.
3.- Write in the cell €œG1€ description (located in the column "A") of the
row found.

In my sample the selected row is number 5 which is cell €F1=63€ and cell €œH1
= top€.
Do you thing you can help me to get a formula to automate this procedure?

Thanks in advance.
Maperalia



PCLIVE
Formula to Match Values
 
Here's one way:

=INDIRECT("A"&MATCH(F1,E1:E6,0))

However, what if there is not a match of F1 in E1:E6?

HTH,
Paul

--

"Maperalia" wrote in message
...
I wonder if you can help me to setup this formula.
I have values located in the excel cell as shown below:

A B C D E F G
1 bag 24 24 24 72 63 top
2 tube 24 24 21 69
3 cord 24 24 18 66
4 top 24 21 18 63
5 center 24 21 15 60
6 paper 24 21 12 57


Basically, what I am doing is:
1.- Taking the sum for each row (B+C+D).
2.- Compare and match values on the column "E" with the number located in
the cell " F1".
3.- Write in the cell "G1" description (located in the column "A") of the
row found.

In my sample the selected row is number 5 which is cell "F1=63" and cell
"H1
= top".
Do you thing you can help me to get a formula to automate this procedure?

Thanks in advance.
Maperalia

T. Valko
Formula to Match Values
 
Try this:

=INDEX(A1:A6,MATCH(F1,E1:E6,0))

--
Biff
Microsoft Excel MVP


"Maperalia" wrote in message
...
I wonder if you can help me to setup this formula.
I have values located in the excel cell as shown below:

A B C D E F G
1 bag 24 24 24 72 63 top
2 tube 24 24 21 69
3 cord 24 24 18 66
4 top 24 21 18 63
5 center 24 21 15 60
6 paper 24 21 12 57


Basically, what I am doing is:
1.- Taking the sum for each row (B+C+D).
2.- Compare and match values on the column "E" with the number located in
the cell " F1".
3.- Write in the cell "G1" description (located in the column "A") of the
row found.

In my sample the selected row is number 5 which is cell "F1=63" and cell
"H1
= top".
Do you thing you can help me to get a formula to automate this procedure?

Thanks in advance.
Maperalia

maperalia
Formula to Match Values
 
Paul;
I used you formula and it working only when I place the data starting from
A1. If the data starts at A2 the formula snap the information.

Thanks very much.
Maperalia

"PCLIVE" wrote:

Here's one way:

=INDIRECT("A"&MATCH(F1,E1:E6,0))

However, what if there is not a match of F1 in E1:E6?

HTH,
Paul

--

"Maperalia" wrote in message
...
I wonder if you can help me to setup this formula.
I have values located in the excel cell as shown below:

A B C D E F G
1 bag 24 24 24 72 63 top
2 tube 24 24 21 69
3 cord 24 24 18 66
4 top 24 21 18 63
5 center 24 21 15 60
6 paper 24 21 12 57


Basically, what I am doing is:
1.- Taking the sum for each row (B+C+D).
2.- Compare and match values on the column "E" with the number located in
the cell " F1".
3.- Write in the cell "G1" description (located in the column "A") of the
row found.

In my sample the selected row is number 5 which is cell "F1=63" and cell
"H1
= top".
Do you thing you can help me to get a formula to automate this procedure?

Thanks in advance.
Maperalia

maperalia
Formula to Match Values
 
Biff;
Thanks for the formula. I plotted it and it is working PERFECTLY!!!!!.
However, I noticed that when it does not find the match number the formula
shows me "#N/A" ".
Could you please tell me how to add to the formula to tell me "NO MATCH WAS
FOUND".?

Kind regards.
Maperalia


"T. Valko" wrote:

Try this:

=INDEX(A1:A6,MATCH(F1,E1:E6,0))

--
Biff
Microsoft Excel MVP


"Maperalia" wrote in message
...
I wonder if you can help me to setup this formula.
I have values located in the excel cell as shown below:

A B C D E F G
1 bag 24 24 24 72 63 top
2 tube 24 24 21 69
3 cord 24 24 18 66
4 top 24 21 18 63
5 center 24 21 15 60
6 paper 24 21 12 57


Basically, what I am doing is:
1.- Taking the sum for each row (B+C+D).
2.- Compare and match values on the column "E" with the number located in
the cell " F1".
3.- Write in the cell "G1" description (located in the column "A") of the
row found.

In my sample the selected row is number 5 which is cell "F1=63" and cell
"H1
= top".
Do you thing you can help me to get a formula to automate this procedure?

Thanks in advance.
Maperalia

PCLIVE
Formula to Match Values
 
One way,

=IF(ISERROR(MATCH(F1,E1:E6,0)),"",INDEX(A1:A6,MATC H(F1,E1:E6,0)))

Regards,
Paul


--

"Maperalia" wrote in message
...
Biff;
Thanks for the formula. I plotted it and it is working PERFECTLY!!!!!.
However, I noticed that when it does not find the match number the formula
shows me "#N/A" ".
Could you please tell me how to add to the formula to tell me "NO MATCH
WAS
FOUND".?

Kind regards.
Maperalia


"T. Valko" wrote:

Try this:

=INDEX(A1:A6,MATCH(F1,E1:E6,0))

--
Biff
Microsoft Excel MVP


"Maperalia" wrote in message
...
I wonder if you can help me to setup this formula.
I have values located in the excel cell as shown below:

A B C D E F G
1 bag 24 24 24 72 63 top
2 tube 24 24 21 69
3 cord 24 24 18 66
4 top 24 21 18 63
5 center 24 21 15 60
6 paper 24 21 12 57


Basically, what I am doing is:
1.- Taking the sum for each row (B+C+D).
2.- Compare and match values on the column "E" with the number located
in
the cell " F1".
3.- Write in the cell "G1" description (located in the column "A") of
the
row found.

In my sample the selected row is number 5 which is cell "F1=63" and
cell
"H1
= top".
Do you thing you can help me to get a formula to automate this
procedure?

Thanks in advance.
Maperalia

T. Valko
Formula to Match Values
 
Try this:

=IF(COUNTIF(E1:E6,F1),INDEX(A1:A6,MATCH(F1,E1:E6,0 )),"No Match Found")


--
Biff
Microsoft Excel MVP


"Maperalia" wrote in message
...
Biff;
Thanks for the formula. I plotted it and it is working PERFECTLY!!!!!.
However, I noticed that when it does not find the match number the formula
shows me "#N/A" ".
Could you please tell me how to add to the formula to tell me "NO MATCH
WAS
FOUND".?

Kind regards.
Maperalia


"T. Valko" wrote:

Try this:

=INDEX(A1:A6,MATCH(F1,E1:E6,0))

--
Biff
Microsoft Excel MVP


"Maperalia" wrote in message
...
I wonder if you can help me to setup this formula.
I have values located in the excel cell as shown below:

A B C D E F G
1 bag 24 24 24 72 63 top
2 tube 24 24 21 69
3 cord 24 24 18 66
4 top 24 21 18 63
5 center 24 21 15 60
6 paper 24 21 12 57


Basically, what I am doing is:
1.- Taking the sum for each row (B+C+D).
2.- Compare and match values on the column "E" with the number located
in
the cell " F1".
3.- Write in the cell "G1" description (located in the column "A") of
the
row found.

In my sample the selected row is number 5 which is cell "F1=63" and
cell
"H1
= top".
Do you thing you can help me to get a formula to automate this
procedure?

Thanks in advance.
Maperalia

maperalia
Formula to Match Values
 
Biff;
Thanks very much.It is working perfectly!!!!!!!!

Maperalia

"T. Valko" wrote:

Try this:

=IF(COUNTIF(E1:E6,F1),INDEX(A1:A6,MATCH(F1,E1:E6,0 )),"No Match Found")


--
Biff
Microsoft Excel MVP


"Maperalia" wrote in message
...
Biff;
Thanks for the formula. I plotted it and it is working PERFECTLY!!!!!.
However, I noticed that when it does not find the match number the formula
shows me "#N/A" ".
Could you please tell me how to add to the formula to tell me "NO MATCH
WAS
FOUND".?

Kind regards.
Maperalia


"T. Valko" wrote:

Try this:

=INDEX(A1:A6,MATCH(F1,E1:E6,0))

--
Biff
Microsoft Excel MVP


"Maperalia" wrote in message
...
I wonder if you can help me to setup this formula.
I have values located in the excel cell as shown below:

A B C D E F G
1 bag 24 24 24 72 63 top
2 tube 24 24 21 69
3 cord 24 24 18 66
4 top 24 21 18 63
5 center 24 21 15 60
6 paper 24 21 12 57


Basically, what I am doing is:
1.- Taking the sum for each row (B+C+D).
2.- Compare and match values on the column "E" with the number located
in
the cell " F1".
3.- Write in the cell "G1" description (located in the column "A") of
the
row found.

In my sample the selected row is number 5 which is cell "F1=63" and
cell
"H1
= top".
Do you thing you can help me to get a formula to automate this
procedure?

Thanks in advance.
Maperalia

maperalia
Formula to Match Values
 
Paul;
Thanks very much.It is working perfectly!!!!!!!!

Maperalia

"PCLIVE" wrote:

One way,

=IF(ISERROR(MATCH(F1,E1:E6,0)),"",INDEX(A1:A6,MATC H(F1,E1:E6,0)))

Regards,
Paul


--

"Maperalia" wrote in message
...
Biff;
Thanks for the formula. I plotted it and it is working PERFECTLY!!!!!.
However, I noticed that when it does not find the match number the formula
shows me "#N/A" ".
Could you please tell me how to add to the formula to tell me "NO MATCH
WAS
FOUND".?

Kind regards.
Maperalia


"T. Valko" wrote:

Try this:

=INDEX(A1:A6,MATCH(F1,E1:E6,0))

--
Biff
Microsoft Excel MVP


"Maperalia" wrote in message
...
I wonder if you can help me to setup this formula.
I have values located in the excel cell as shown below:

A B C D E F G
1 bag 24 24 24 72 63 top
2 tube 24 24 21 69
3 cord 24 24 18 66
4 top 24 21 18 63
5 center 24 21 15 60
6 paper 24 21 12 57


Basically, what I am doing is:
1.- Taking the sum for each row (B+C+D).
2.- Compare and match values on the column "E" with the number located
in
the cell " F1".
3.- Write in the cell "G1" description (located in the column "A") of
the
row found.

In my sample the selected row is number 5 which is cell "F1=63" and
cell
"H1
= top".
Do you thing you can help me to get a formula to automate this
procedure?

Thanks in advance.
Maperalia

T. Valko
Formula to Match Values
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Maperalia" wrote in message
...
Biff;
Thanks very much.It is working perfectly!!!!!!!!

Maperalia

"T. Valko" wrote:

Try this:

=IF(COUNTIF(E1:E6,F1),INDEX(A1:A6,MATCH(F1,E1:E6,0 )),"No Match Found")


--
Biff
Microsoft Excel MVP


"Maperalia" wrote in message
...
Biff;
Thanks for the formula. I plotted it and it is working PERFECTLY!!!!!.
However, I noticed that when it does not find the match number the
formula
shows me "#N/A" ".
Could you please tell me how to add to the formula to tell me "NO MATCH
WAS
FOUND".?

Kind regards.
Maperalia


"T. Valko" wrote:

Try this:

=INDEX(A1:A6,MATCH(F1,E1:E6,0))

--
Biff
Microsoft Excel MVP


"Maperalia" wrote in message
...
I wonder if you can help me to setup this formula.
I have values located in the excel cell as shown below:

A B C D E F G
1 bag 24 24 24 72 63 top
2 tube 24 24 21 69
3 cord 24 24 18 66
4 top 24 21 18 63
5 center 24 21 15 60
6 paper 24 21 12 57


Basically, what I am doing is:
1.- Taking the sum for each row (B+C+D).
2.- Compare and match values on the column "E" with the number
located
in
the cell " F1".
3.- Write in the cell "G1" description (located in the column "A")
of
the
row found.

In my sample the selected row is number 5 which is cell "F1=63" and
cell
"H1
= top".
Do you thing you can help me to get a formula to automate this
procedure?

Thanks in advance.
Maperalia


All times are GMT +1. The time now is 05:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com