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!!

 

[Loreleii7]

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")

It's a good solution but I don't have the percentages as number, but as text. Because I need it to be shown exactly as "-35%".

I thought it would be a simple vlookup function which finds the * on the column B and show me the cell on its right... why it doesn't work?

 

[wrightyrx7]

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

This is exactly what Seti's formula does. Maybe you just need to expand the range.

put this in D1
=INDEX(C:C,MATCH("*",B:B,0))

 

[Loreleii7]

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?

Yes, that's exactly what I need to do, but I'm afraid it's not working well. It works only for the row 1, if the * is on the row 2, it doesn't work anymore.

 

[Loreleii7]

This is exactly what Seti's formula does. Maybe you just need to expand the range.

put this in D1
=INDEX(C:C,MATCH("*",B:B,0))

It works exactly the same than my formula. Only works for the row 1.

 

[Loreleii7]

OK, I guess I'll use a nested IF's formula... thought it would be simpler...

Thanks so much for your cooperation!!

 

[FormR]

Try:

=VLOOKUP("~*",B1:C3,2,FALSE)

The tidal character tells Excel not use the * as a wild character.

 

[Loreleii7]

Try:

=VLOOKUP("~*",B1:C3,2,FALSE)

The tidal character tells Excel not use the * as a wild character.

YESSSS!! It works!!!
That was the problem. Thank you so much!