Vlookup - help!!!!

[Loreleii7]

Hi Everyone,

I'll try to explain the more clear I can what I need to do, hope you can help me!

I have a table as follows:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]1,000[/TD]
[TD="align: center"]*[/TD]
[TD]-37.5%[/TD]
[TD]-37.5%[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]2,500[/TD]
[TD][/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]5,000[/TD]
[TD][/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The column A is a list of amounts from which we can choose from a drop down list (data validation). On the column B there are formulas entered to show a "*" besides the amount that was selected on the cell with the drop down list. The column C shows a percentage as the result of a formula relating the cell on the column A with another one on the spreadsheet (if the amount 2,500 - column A - would have been selected instead of 1,000, the "*" would have show up on that row and also the percentage).

So, what I need to do on the cell D1 is to show the cell that is showing a percentage and not a "N/A".

Currently, I'm using the following formula: =VLOOKUP("*",B1:C3,2,FALSE)

But I'm afraid that it only works for the first row (1,000)... what I'm a doing wrong?

Thank you!!

 

[L. Howard]

If I understand correctly try this in D1 and pull down.

=IF(B1="*",C1,"")

Vlookup will only return the first value in the lookup column if there are duplicates in that column.

Regards,
Howard

 

[Loreleii7]

Thank you!
However, what I actually need is that D1 look for the only percentage shown on the column B (there will always be ONE cell showing a percentage, the rest will be always "N/A")

 

[wrightyrx7]

Try this

=IF(B1="*",IF(ISNA(C1),"",C1),"")

 

[Seti]

Try this in D1: =INDEX(C1:C3,MATCH("*",B1:B3,0))

 

[Loreleii7]

Try this

=IF(B1="*",IF(ISNA(C1),"",C1),"")

Thank you!! But, as I told to L. Howard, what I actually need is that D1 look for the only percentage shown on the column B (there will always be ONE cell showing a percentage, the rest will be always "N/A")

 

[Loreleii7]

Try this in D1: =INDEX(C1:C3,MATCH("*",B1:B3,0))

Thank you, Seti!!
But it does the same than my formula. It only works for one of the rows... I'm sorry.

 

[Seti]

I'm not sure what you mean here. My code looks for the * in column B and pulls the value from that row in column C. I am assuming that you only want cell D1 to have a formula so that it finds the percentage in column C. Can you provide an example of what you are getting when you try different things?

 

[wrightyrx7]

Thank you!! But, as I told to L. Howard, what I actually need is that D1 look for the only percentage shown on the column B (there will always be ONE cell showing a percentage, the rest will be always "N/A")

I think people are finding it difficult to understand what it is you want.

What do you want column D to do?

Row 1 - IF B1 equals "*" and C1 contains a % put the % in D1
Row 2 - IF B2 equals "*" and C2 contains a % put the % in D2
Row 3 - IF B3 equals "*" and C3 contains a % put the % in D3

an so on??

 

[wrightyrx7]

Thank you!
However, what I actually need is that D1 look for the only percentage shown on the column B (there will always be ONE cell showing a percentage, the rest will be always "N/A")

If there will only ever be one cell in column "C" with a percentage in, and the rest N/A why not total column C in D1 but ignore any cells containing N/A

=SUMIF(C:C,"<>#N/A")