Vookup circular reference problem

[Darko42]

Hi,

I'm looking for a way to quickly fill in prices in tables.
Column A has the item, B has the price.
In column A items repeat multiple times.
Column B is initially blank in witch I put the Vlookup formula.
I want to manually fill in the prices, starting from top and replacing the formula cell. If the item in column A appears again it should automatically get the previous value with the formula and I skip it and continue down.

Basically my formula does work. The problem is the tables are not mine, I receive them from multiple people and it may contain up to 10000 rows(of which 300 are unique), and the items are always different. So sometimes this formula works and sometimes it gets stuck or something, I guess because of the circular reference problem. I tried many sugested thing for the cilcular refrence but non helped in the long run.

Does someone have an alternative or an fix for my formula? VBA/Macro is also acceptable.

Darko

[Richard Buttrey]

Would you show the results you expect and where, and explain how you calculate the results. It's not clear what the stuff is in columns G:J

[Darko42]

In the example in the column A-D is the data I have and the function i put in.
The column G-J is the same as A-D but with field in data in column I. This is how it should look like.
In this example the formula works ( I gues due to small number of data) but I still get the circular reference notification.
I would like to do it without the circular reference if posible.
Hope this helps to clarify

[jason.b75]

Try this in C3 and fill down

=VLOOKUP(A3,A$2:C2,3,FALSE)

Initially it will show a column of #N/A errors, when you override the first one with an actual price, it will clear all errors for the same item.

Is that what you wanted?

[avk]

Try in "C3"

Please Login or Register  to view this content.

Instead of vlookup i am suggest index match.

[Richard Buttrey]

If you're just seeking the last listed price for each fruit listed in the G:J table then in C3

Please Login or Register  to view this content.

But that will only work when the column G fruits are in alphabetical order, either in the list as a whole or their individual sections.

But this is clearly a much simplified example and probably doesn't represent the size of nor conain your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

It's not clear to me whether the G:J table is your actual data and you are wanting to derive A:D. What you can't do is have formulae in column I which refer to the A:D columns and also have column C:D formulae which refer to columns G:J and NOT have a circular reference

[Darko42]

Try this in C3 and fill down

=VLOOKUP(A3,A$2:C2,3,FALSE)

Initially it will show a column of #N/A errors, when you override the first one with an actual price, it will clear all errors for the same item.

Is that what you wanted?

Thank you very much. This is exactly what I have been looking for.
There is no more circular reference. I will test is on a few actual data and let you know. But I think this is it.