[ptbuster]

I have a lookup table and I need a formula that looks at (example) h14 then i14 then looks up the table and returns the value associated with that row. Here is part of my formula, but its lacking something.
=IF(h14<0.01," ",LOOKUP(h14,$A$368:$A$711,$G$368:$G$711))

Can anyone help, please.
Thanks

[ebaines]

The formula you typed checks to see if h14 <.01, and if it is it looks for the value of h14 in the column a368:a711. And then returns the value from the corresponding row in column g. Is that what you want? What is it that you want from i14?

[ptbuster]

The formula you typed checks to see if h14 <.01, and if it is it looks for the value of h14 in the column a368:a711. and then returns the value from the corresponding row in column g. Is that what you want? What is it that you want from i14?

FORMULA TEST
H14 I14 J14 L14 M14 N14
CODE GRADE A Value X Sq. Ft =Total value



DK3 CONCRETE DOCK Rate
1
2
3

How I want this to work is when I type DK3 into cell H14 then I want it to find the number I type into I14 that will give me a value for that code in L14. The value in L14 is the value per sq. ft. that will be multiplyed by the sq. ft I put in M14 to give me a total value.

I want to find col A then find col B to give me the value in col G.

I hope this explains what I am trying to do.

Thanks,

[ebaines]

Let me restate what I think you are trying to say:

You enter values into H14 and i14. If the value of H14 is "DK3" you want to lookup the value of i14 in column A of the table and return the coresponding value from column G in that table. If the value in H14 is something other than DK3, what's supposed to happen? I'll assume for now that you want nothing to be returned in L14.

In this case you can use VLOOKUP, and the formula in cell L14 ought to be:
if(h14="DK3", VLOOKUP(i14,$A$368:$G$711,7),"")

This assumes that the DK3 table has the price you're trying to look up in column G (the 7th columne of the table A368:g711).

Then in cell N14 you put the formula =L14*m14

Hope this helps.

[ptbuster]

Hi ebaines, thanks for replying.

I guess I wasn't to clear as to what I'm trying to accompolish.

In col J I want the formula to look in the table for any code I put in col A, when it finds that code in col A I want it to look for a code in col B. When A & B match up I want it to give me the value in col G to be placed in the formula cell.
From there I will multiply the SF by that value to get my total value.

Thanks again for looking at this, I hope this is do-able.

[ebaines]

Are you saying that the value in column A and the value in column B must both match what's specified, then it returns the value in column G? If you're trying to match two values, it may be better to set your data up in a table with you're a values in column A and your B values as the first row across, and then the intersectoin of the two is the value you want to return. Would that work for your application? For example if your table is like this:

- 1 2 3 4
A b c d
B e f g h
C I j k l

Then you can use INDEX and MATCH to find that the value to return for (B,3) is g, and the value for (A,1) is 1, etc. You coud use something like:
=INDEX(a20:L40,MATCH(H14,a20:a40,0),MATCH(I14,a20: L20,0))

Here the H and I values are in column A and rown 20, respectively, and your return values are in the table columns B through L rows 21 through 40.

[ptbuster]

Hi ebains, thanks again for your interest in helping me. I've read a lot of help info but cannot put together what I'm trying to do.

As you can tell my look up table is from cells 368 to 711 in different columns.

Col A may have 50 codes such as DK3 or UB2, etc. with 5 or 6 blank cells between in that col.

Col B will have the numbers 1, 2, 3, 4, 5 going down one number in each cell for each code that appears in col A.

Col G has a value associated with each cell associated with that row.

My query would be something like this.
In cell H14 I will type DK3, in cell I14 I will type 2. When I type these two codes they will put the value in col G associated with them into my formula cell.

A B G Formuls cell
Example: DK3 2 $13.25 $13.25
UB2 3 $15.56 $15.56

etc. etc.


Thanks, again for your help.

[ScottGem]

Can you post a copy of your spreadsheet? You can zip it and attach to a post here. That might help.

If I understand what you are trying to do, the only way I can see doing this is to add a column to your lookup table and to your data that concatenates the two values. So lets say you have DK3 in the leftmost column and numbers 1-6 in the second column. You would need to have a THIRD column that concatenates the two (i.e. DK31) And you would use THAT column as the lookup column. In your data table you would also have a third column that concatenates the two to create the lookup value.

[ebaines]

Scott gives an excellent suggestion. I don't see any other (easy) way to do a 2-part lookup like this with one formula. If you had a only a few choices for codes in column A you could use a set of nested IF statements, each with its own VLOOKUP range, but that won't work if you have more than a few choices of values for A.

[ptbuster]

Hi Scottgem, ebaines;

I appreciate your responses and it has got me trying what scottgem suggested, but it does not always give me the correct value. I cannot seem to zip the file or I just don't know how, so I can't post it here. So I'm reading more about formulas to see if I can figure it out. I feel it is a fairly simple thing that should work, but only if I have the correct formula.
Thanks again for your interest, this is a great help desk and people are willing to gladly help others.

thanks again.

[ScottGem]

If you don't have a Zip utility, you can right click the file and choose Send To, then select Compressed folder.

[ptbuster]

Hi Scottgem & ebaines,

I worked out the formula after Scottgem kind of gave me the idea for the two columns to make the third into a new lookup column. What I did was set up my vlookup table making my first column with the new code as if it were the results in the third column. Then in the second column I put in my dollar values. Then in the formula cell I put in the formula with a cell next to it to put in the lookup code to return the associated value with it. I then copied that formula down the column for the amount of rows I was using. So the formula looked like this. =vlookup(h12,lookup,2,false) and it works correctly every time. Although it took me a couple days and a few hours to work it out after the idea was given to me, it was well worth it.

Thanks a million guys for the help, I learned a lot on this one.
Keep up the good work and interest in helping others.
Much appreciated.

[ScottGem]

That sounds basically what I suggested you do. Glad you got it.