Google Sheets: VLOOKUP help needed

[r3mmi]

Dear All,

I need help with google docs document,
I've made an example for you, hovewer original doc that I'm using now is a lot bigger, like its 3k rows,

Example: can't post links ( attached excel document, thank you moderator )

Can't do it with formulas
So I will explain.

L4 and down to L90 is =iferror(IF(C4=VLOOKUP(C4,from,1,TRUE),VLOOKUP(C4,from,2,TRUE),NA()))

P4 to P90 is =iferror(IF(G4=VLOOKUP(G4,to,1,TRUE),VLOOKUP(G4,to,2,TRUE),NA()))

this is double vlookup that I've found in internet, (to and from are named ranges)

The old one, used from L90 and P90 is looking like this
=IFERROR(VLOOKUP(C92,$C$1:$P$5981,10,FALSE))
and
=IFERROR(VLOOKUP(G92,$G$4:$P$5981,10,FALSE))

as you can see range is wide, since now I completed about 3k rows, and completing +-100-200 everyday


So here is the question,

My spreadsheet is working like it's in row 92 and bottom,
So what it has to do,
I'm introducing info in cells c92 and g92, it vlookups the table and searching if something same was introduced before and give me an option,
However, when it got to be big, like 3k rows, it takes a lot of time,


I've managed to find an info that double vlookup with true criteria(sorted data) work faster,
So I've made it in first rows, like from 04
It works good, and it takes info from INDEX sheet(which I've made by miself from my large data and sorted it)

But in original docs, It's not going to INDEX sheet, its not getting sorted,

Any suggestions?

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook. As a new member, you can't post links: it's a SPAM reduction filter, so you'll have to use the attachment facility at the moment.

[r3mmi]

Dear AliGW, thank you for your reply, I changed my post, so there is an attachment

[FDibbins]

Perhaps you uploaded the wrong file? In the attached file, none of the references you quote, contain the formulas you show?

However, this part of the formula seems to make no sense...
IF(C4=VLOOKUP(C4,from,1,TRUE)
you are basically saying if C4 = itself?

and the rest of the formula...
IF(C4=VLOOKUP(C4,from,1,TRUE),VLOOKUP(C4,from,2,TRUE),
if C4 = itself, then return the value from the next column over?

[hrlngrv]

If you're not using exact matches because of recalculation efficiency, you'd realize much greater efficiency using 2 formulas per result. Also, it appears B4:B = K4:K and F4:F = O4:O, and you're not using the values in E4:E, I4:I. I'd eliminate redundancies as follows.

D4:

Please Login or Register  to view this content.

E4:

Please Login or Register  to view this content.

H4:

Please Login or Register  to view this content.

I4:

Please Login or Register  to view this content.

B4:

Please Login or Register  to view this content.

F4:

Please Login or Register  to view this content.

K4:

Please Login or Register  to view this content.

O4:

Please Login or Register  to view this content.

N4:

Please Login or Register  to view this content.

R4:

Please Login or Register  to view this content.

L4:

Please Login or Register  to view this content.

P4:

Please Login or Register  to view this content.

Fill each of these down to fill their respective columns. Clear the formulas in M4:M and Q4:Q since they're unnecessary.

See this example from my Google Drive.

[hrlngrv]

. . . However, this part of the formula seems to make no sense...
IF(C4=VLOOKUP(C4,from,1,TRUE)
you are basically saying if C4 = itself?

Let from be {0,"foo";9,"bar"}. Let C4 be 5. What does C4=VLOOKUP(C4,from,1,TRUE) return?

[FDibbins]

Let from be {0,"foo";9,"bar"}. Let C4 be 5. What does C4=VLOOKUP(C4,from,1,TRUE) return?

OK, perhaps I could have explained that better...

Unless VLOOKUP(C4,from,1,TRUE) returns exactly what is in C4, it will return FALSE. So it will either give C4 contents or an error

[hrlngrv]

. . . Unless VLOOKUP(C4,from,1,TRUE) returns exactly what is in C4, it will return FALSE. So it will either give C4 contents or an error

It only returns an error if C4 is less than the top-left cell in from.

The 2-VLOOKUP idiom is meant to use range rather than exact lookups in tables with 1st columns sorted in ascending order for speed, but compare x with VLOOKUP(x,y,1) to check for equality. The assumption being that 2 O(log(N)) range lookups would be faster than 1 O(N) exact lookup.

In the rhetorical example I gave in my last reply, the VLOOKUP call would return 0, which wouldn't equal 5, so the comparison would be false, indicating an exact match didn't exist.

I don't use this idiom myself, but I've seen it in spreadsheets others have built. It's a bit more nuanced than you seem to believe.

[r3mmi]

Perhaps you uploaded the wrong file? In the attached file, none of the references you quote, contain the formulas you show?

However, this part of the formula seems to make no sense...
IF(C4=VLOOKUP(C4,from,1,TRUE)
you are basically saying if C4 = itself?

and the rest of the formula...
IF(C4=VLOOKUP(C4,from,1,TRUE),VLOOKUP(C4,from,2,TRUE),
if C4 = itself, then return the value from the next column over?

To be honest, I tried this formula as an example, since I found it about an hour before writing this question for you here, and it's kinda working, working by taking range for data from index sheet. However (go under next quote reply)

If you're not using exact matches because of recalculation efficiency, you'd realize much greater efficiency using 2 formulas per result. Also, it appears B4:B = K4:K and F4:F = O4:O, and you're not using the values in E4:E, I4:I. I'd eliminate redundancies as follows.

D4:

Please Login or Register  to view this content.

E4:

Please Login or Register  to view this content.

H4:

Please Login or Register  to view this content.

I4:

Please Login or Register  to view this content.

B4:

Please Login or Register  to view this content.

F4:

Please Login or Register  to view this content.

K4:

Please Login or Register  to view this content.

O4:

Please Login or Register  to view this content.

N4:

Please Login or Register  to view this content.

R4:

Please Login or Register  to view this content.

L4:

Please Login or Register  to view this content.

P4:

Please Login or Register  to view this content.

Fill each of these down to fill their respective columns. Clear the formulas in M4:M and Q4:Q since they're unnecessary.

See example from my Google Drive.

This is working much more efficient than the one I did, since it's using less formulas, so I think it has to work quicker,
However, since you helped me with this so good, and explained everything,
I really hope I can do it. Can I upload excel document with the link to my google spreadsheet? Or its prohibited, let me know I don't want to be banned I like to talk to you.

So I will explain why I want to upload and show you something.

Basically, I'm looking for column L to take data not only if it maches from database from index sheet, but also if I introduced new one by miself in example sheet.
For example, as you can see, there are different countries, and if I will intoduce(C4:C somewhere in this column) one day new country with the code lets say MD, as my country, it will not find nothing in index database and will not return anything(L4:L here), and I will have to introduce it myself(L4:L)

So I want this somehow to search in database from index, that I will try to add info from example sheet to index one, so I will have more empty database in example sheet and more advanced one in index sheet,
But basically, I'm working only in example sheet, and I want it to search in same directory, and work quick

I hope its possible,

Also let me know if I can upload excel doc with the link inside.
Thank you

[AliGW]

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

[r3mmi]

Thank you very much, will deffinitely do so next time,
Since you are moderator here, can I have an answer if I can post excel doc in attachment with link to my google sheet?
I want to do everything by the rules

[hrlngrv]

I'm not a moderator, and I don't try to do their job.

If you can post urls to SHARED Google Sheets documents, it'd be much easier to open such document IN GOOGLE SHEETS in order to review them. However, if you can't post links, possibly because your account is too new, you may be able to post PARTIAL urls. For example, the link in my previous reply was

https://docs.google.com/spreadsheets/d/1U6-4FWMyvD9iXvNh02Y6VPyw-PDjvqVhv6KlOSazPTY/edit?usp=sharing

You'd only need to provide the part in red text, which is unlikely to be a rules violation (but I'll let the moderators opine on that), and I'd be able to construct the necessary url for your workbook.

[r3mmi]

Dear hrlngrv,

Thank you very much for this tip, here is the part you need d/1__Vz62C9NkSFEmAUU-yM8wgyqEMxyiPOBuRJRiijbqs

So basicaly, my document is working right now as it follows from 92nd row, you can especially see it as an example in P93 and P94
since P93 is introduced manually, and P94 is taking value from P93 since it was introduced earlier

Thank you

[AliGW]

You'd only need to provide the part in red text, which is unlikely to be a rules violation (but I'll let the moderators opine on that),

Our opinion is that it is not.