excel question

[prophetik music]

hey, folks. hopefully someone here knows more about excel than me.

i'm trying to get a formula that'll reference two data points in different columns in the same row, and output a third data point in a different column of the same row. so, for example, i've got ten rows. i want it to look in rows for two specific data points in columns A and B, and when it finds a row with both, spit out whatever's in column C in that row into a box. i can't seem to get lookup, vlookup, match, index, or a combo of them to work properly. the biggest issue i have is that while column A is in numerical order, there are tons of repeats of the datapoint in column A (hence why i need B to be referenced as well).

how do i do this?

[phill]

Assuming I understood what you are trying to do here, this is what I came up with

http://mephisto-ocr.angryitch.com/ForProph.xlsx

[Nutritious]

Didn't look at what Phil did, but anyway.

Assuming I understand what you're trying to do, you could concatenate A and B in a separate column (D), then vlookup on D for the values you want & return the corresponding C value.

[prophetik music]

phill - right idea, but your thingie doesn't work. try looking for 61 in A - it doesn't return anything, even though there's two with 125 in the second column. even if i change one of them to 124, so that there's only one combo of 61 and 125, it returns 0.

nutritious - i'd prefer not to, since we're talking about 15k rows of data. i can't manually assign it special IDs without it taking for eeeeeever. unless you know a way to automatically combine two sets of datainto a stacked set? like, A is 24 and B is F, so C is 24F? because i might be able to make that work, although it'll be a butcher job.

i guess i'm looking for a vlookup(DataInColumnA AND DataInColumnB) function. i'd think it's doable with an if function, but i don't know how to tell it to run the if row-by-row until it finds the right information. i tried to get an index or a match function to work in there [vlookup(match(somethingorother), match(somethingorother2)), but i couldn't figure out how to make it work. there doesn't appear to be an if-then-else function, just if A, else B function.

i guess i have to do it sloppy, by having two references, then having a stacked set of if(then) statements. i was hoping for a simple solution, or one that i knew would work, since i don't know if i can make it work without searching the whole thing every time. thanks for the help, guys =)

[phill]

Yeah...doesn't work because of duplicate values in A, forgot about that. Given the size of your dataset, I would assume you are going to have duplicate values in the first column and as a result the easiest way to achieve what you want is to create a macro.

[WaywardSon]

so, for example, i've got ten rows. i want it to look in rows for two specific data points in columns A and B, and when it finds a row with both, spit out whatever's in column C in that row into a box.

Are you looking for a unique combo of A and B column values, and nothing else? For instance, 24 in A and F in B, and where those two pair up? If so, you might want to try a combo of IF, VLOOKUP and CONCATENATE.

Something like this:

=if(concatenate(a1,b1)="value_you_want",vlookup(concatenate(a1,b1),table_array,3),"")

The third argument for vlookup is 3, because I'm assuming the value you're looking for is in the third column, C.

Since you say you have about 15k rows, you might want to consider making a macro or something. So, in a way, this is a mashup of what both phil and Nutritious were suggesting.

[prophetik music]

actually, wayward, that looks like what i want. can you notate that code a little more so that i can see where to plug stuff in?

to be specific, the array is A40 to roughly M15750, with A being one set of numbers, B being one set of numbers, and G being the data i want pulled.

[Nutritious]

nutritious - i'd prefer not to, since we're talking about 15k rows of data. i can't manually assign it special IDs without it taking for eeeeeever. unless you know a way to automatically combine two sets of datainto a stacked set? like, A is 24 and B is F, so C is 24F? because i might be able to make that work, although it'll be a butcher job.

Well, right, that's what concatenate does :/. Go to another column next to your data and hit the formula button. Search for Concatenante. It'll bring up a little box that'll walk you through it. This will automatically combine data from both cells into a third cell.

Anyway, wayward son is suggesting the same thing, just doing it all in one function - so that may be easier for you.

[prophetik music]

actually, i figured out a different way. it's a little sloppier, but it makes it work with the broken database i got stuck with. so i'm good for now =)

thanks for the info, justin - i had no idea what concatenate did originally, so that helps a lot.

[WaywardSon]

Something like this:

=if(concatenate(a1,b1)="value_you_want",vlookup(concatenate(a1,b1),table_array,3),"")

The third argument for vlookup is 3, because I'm assuming the value you're looking for is in the third column, C.

My bad, you don't actually need to use VLOOKUP, because you could get what you want with just IF and CONCATENATE (and the formula is simpler):

=if(concatenate(a40,b40)="value_you_want",g40,"")

A40, B40 and G40 because that's where your data starts. And the formula assumes that you're always looking for the same value, no matter what. You could also put that value in a fixed position (for example AA1), in which case the "value_you_want" becomes $AA$1.

I guess I'm a little late with this correction, but what the heck, you might find some use for it.

[prophetik music]

i wound up using a few functions - primarily MATCH and INDEX - to just make it work with a few databoxes in the corner out of site. so it works now =)

thanks anyways, guys, that concatenate code will be nice for future stuff i've got planned.

[phill]

If this sort of thing is going to become common and given the fact you are working with ~15k records, you may want to consider switching to a database of some sort. Even Access would be better then a massive spreadsheet.

[prophetik music]

i believe that. i need it in excel, however, which is why i've gotta keep it where it is.